Time-Based Trigger Troubleshooting

119 views
Skip to first unread message

Aaron Dannenbaum

unread,
Feb 13, 2020, 10:15:42 AM2/13/20
to Google Apps Script Community
Hi all,

I'm attempting to automate the pulling of tables from my company's Looker instance into Google Sheets using time-based triggers, but I'm having a bit of trouble. I found a script on Looker's website that I'm using to import these files, as follows:

function lookerFetchData(url) {
   url
= url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");
   
var csvString = UrlFetchApp.fetch(url).getContentText();
   
var dataIn = Utilities.parseCsv(csvString);
   
var dataOut = dataIn.map(function(row) {
   
return row.map(function(val) {
   
if (val == '') return '';
   
var dateMatch = /(\d{4})\-(\d{2})-*(\d{0,})/.exec(val);
   
while (dateMatch != null) {
   
// first index is full match
   
return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);
   
};
   
if (val.match(/[-a-zA-Z]/)) {
   
return String(val)
   
};


   val
= val.replace(/[^\d.]/g, '');


   
if (val.match(/[0-9.]+/))
   
return Number(val);


   
return Number(parseInt(val));
   
});
   
});
   
return dataOut;
 
}

I then added a time-based trigger to have this script run once daily in one of my sheets. However, when it ran, this error was sent to me via email:
TypeError: Cannot find function replace in object {year=2020, month=2, day-of-month=13, day-of-week=4, week-of-year=7, hour=5, minute=56, second=31, timezone=UTC, authMode=FULL, triggerUid=3099827}. (line 2, file "Code")

I'm not sure exactly what the error is asking for - I see a url.replace function within the larger function already. Can somebody help me troubleshoot?

Thanks,
Aaron

--Hyde

unread,
Feb 14, 2020, 3:02:00 AM2/14/20
to Google Apps Script Community
Hi Aaron,

The lookerFetchData custom function is clearly supposed to run from a spreadsheet cell formula as in =lookerFetchData(U2) where cell U2 contains the address from where to fetch data. You cannot refresh these results just by adding a time-driven trigger to run lookerFetchData. Try using Looker's Auto Refresh Script and set up a time-driven trigger to run the Refresh function instead.

Cheers --Hyde

Aaron Dannenbaum

unread,
Feb 14, 2020, 12:33:00 PM2/14/20
to Google Apps Script Community
Thanks a ton Hyde. My hesitation with putting a time-based trigger on Refresh initially is that isn't the function going to be run on open automatically regardless because of the onOpen() function in the Auto Refresh Script that contains Refresh?

--Hyde

unread,
Feb 14, 2020, 2:57:06 PM2/14/20
to Google Apps Script Community
My hesitation with putting a time-based trigger on Refresh initially is that isn't the function going to be run on open automatically regardless because of the onOpen() function in the Auto Refresh Script that contains Refresh?

Yes, but that should not matter much. When you have the time-driven trigger set up, you can comment out the onOpen(e) function if you do not need the menu it creates. In the event your spreadsheet has several tabs, and you need all of them to refresh, point the trigger at RefreshAll() rather than Refresh().

Cheers --Hyde

Aaron Dannenbaum

unread,
Feb 14, 2020, 5:10:36 PM2/14/20
to Google Apps Script Community
I see - didn't realize I had to comment out the onOpen function. It looks to be working when I do that. Thanks a ton for the help!
Reply all
Reply to author
Forward
0 new messages