Time-Based Trigger Troubleshooting

[Aaron Dannenbaum]

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]

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]

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]

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]

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!