How do I get linked Google spreadsheets to auto-update when 'I am not looking' [duplicate]

Question

My question is similar to this one, for which no answer has yet been posted. How can I get Google Sheets to auto-update a reference to another sheet?

I am filling one Google Spreadsheet with Zapier which creates new rows of data. I then use ImportRange to take that some data from that sheet into another, where it is processed and generates text that I use to send out emails or SMS.

This 2nd sheet is read by Zapier in order to send the email or SMS.

The problem is that the 2nd sheet does not automatically update when I am not logged in an making changes. i.e. importrange() is not updating 'when I am not looking'.

I have both sheets set to refresh every minute and Zapier 'reads them' once every 5 mins (writing to the first sheet, only reading from the second").

I have tried any scripts, although I have tried using the Now() function and concatenate() functions in order to amend the data pulled in by importrange() - which did not fix the problem.

The second sheet is a bit complex and takes time to load.

Answer 1

I was able to find a way to solve my problem (detailed here) using an Apps Script with a custom function.

If you replace =IMPORTRANGE(spreadsheet_url, range_string) in your spreadsheet with =DynamicImportRange(spreadsheet_url, sheet_name, range) and paste the code below into Tools --> Script Editor --> Blank Project, it should work (see this for more information on writing apps scripts).

/**
Usage: =DynamicImportRange(spreadsheet_url, sheet_name, range)
Compare to: =IMPORTRANGE(spreadsheet_url, range_string)
 */
function DynamicImportRange(sheet_url, sheet_name, sheet_range) {
  var values = SpreadsheetApp.openByUrl(sheet_url).getSheetByName(sheet_name).getRange(sheet_range).getValues();
  return values
};

/**
*/
function RefreshSheet() {
  // Update the following two variables to suit your particular situation
  var sheet_name = "sheet1"
  var range = "A1"

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var formula1 = "Loading...";
  var formula2 = sheet.getRange("range").getFormula().toString().replace('"', '\"');
  // Note that the following command will delete everything on the sheet.
  // If you do not want to do this, you may need to get creative
  // with how you specify what gets deleted.  You could potentially split
  // out the last argument of "formula2" to find the range that needs cleared.
  sheet.clear();
  sheet.getRange("range").setValue(formula1);
  Utilities.sleep(245);
  sheet.getRange("range").setValue(formula2);
};

If you want the sheet to be updated on a regular basis, you can set up a trigger by going to Resources --> Current project's triggers in the Apps Script window.

Answer 2

You can also use IMPORTRANGE in a conditional formula always TRUE :

=if(now()>0;IMPORTRANGE("1IyAx_*****************************************Lza5x1iVfs"; "Masterlist!AT1:TA39");"CRASH")

Plus you can (in spreadsheet parameters) add an update every minute. The IMPORTRANGE will be updated at least every minute.

Answer 3

I hacked around this by, on both spreadsheets by creating a refresh loop using a NOW cell, with both spreadsheets crossreferencing each other's NOW cell.

When the original sheet gets appended with a form submission or something, it updates its own NOW cell, and reupdates own IMPORTRANGE cell. The second spreadsheet follows suit, updating its own NOW cell to provide the original sheet with the correct data. Because the second spreadsheet has updated itself, it also updates the main IMPORTRANGE which refreshes the data you want it to display in the first place, as well as the IMPORTRANGE cell which gets the NOW cell from the original spreadsheet

At least, I'm pretty sure that's how it works. All I know, and all I care about, frankly, is that it works