CODEWITHSUNDEEP
google-sheetsgoogle-drive-apigoogle-sheets-formulaimportrange
DummyBeginner
DummyBeginner

Reputation: 431

Force IMPORTRANGE to update at certain intervals

I saw several complains about the delay of updating data through IMPORTRANGE in Google Sheets but I need the opposite and don't want the second sheet to get updated automatically, just update at the end of the day for example.

The code is already like this: 

=IMPORTRANGE("The Key","The_Page!B:D")

Upvotes: 1

Views: 6927

Answers (2)

a-change
a-change

Reputation: 666

Maybe you need to use the script editor and write a simple function of the kind:

function importData()
{

var ss = SpreadsheetApp.getActiveSpreadsheet(); //source ss

var sheet = ss.getSheetByName("The_Page");      //opens the sheet with your source data

var values = sheet.getRange("B:D").getValues();   //gets needed values

var ts = SpreadsheetApp.openById("The Key");    //target ss - paste your key

ts.getSheetByName("Name of the target sheet").getRange("B:D").setValues(values);

}

And then add a time-driven trigger to this project (Resources > Current project's triggers > Add a new one).

Upvotes: 2

Jimmy
Jimmy

Reputation: 21

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

Upvotes: 2

Related Questions