Triggered functions and IMPORTRANGE

Question

I have a Google Apps Script that compares information submitted in a Google form to information that is brought into the Google spreadsheet using the IMPORTRANGE function. The script works perfectly if I run it manually but if it runs from the form submit trigger it reports that it isn't getting the correct information from the cells using IMPORTRANGE.

Is there a way around this, like is there a quick function I can add that forces import range to update?

I have already tried to add a wait time of up to 10 minutes and it hasn't changed the results.

Answer 1

The problem is with how functions like importRange works. Actually, they require the same access/permissions as the user account logged in using them, to avoid security holes (imagine an editor of a spreadsheet you own accessing other spreadsheets you have not shared with them).

Because of this (well, at least that's what I think), these formulas are only evaluated when you have the spreadsheet opened. When you close it and leave the script to run on a trigger, it will not find any result on the expected cells.

An easy workaround for this is to fetch the data using the script itself and quit using importRange. It's very easy to do, e.g.

var values = SpreadsheetApp.openById('external-spreadsheet-key').
  getSheetByName('Sheet1').getRange('A1:D100').getValues();
SpreadsheetApp.getActive().getSheetByName('DestSheet').
  getRange(1,1,values.length,values[0].length).setValues(values);

Obviously that the spreadsheet key, sheet's names and ranges are just an example here.