How to force cell recalculation to use ImportRange function in Google spreadsheet?

Question

I have a targeted table that uses ImportRange to retrieve some data from another Source spreadsheet, as in

A6 = query(ImportRange("mykey", "Weekly!B:BI"), CONCATENATE("select * WHERE Col1='",B3,"'"), 0) 

The select clause is to select the data that matches the value in B3 - if this cell value is changed, then the import is updated "immediately".

I want to be able to force update if someone changes something in the source spreadsheet so that we see that this is reflected in Target “immediately”. Currently, this happens only after some indefinite time, which is a minute or so, and too slow for my purposes.

Update:

In accordance with the answer and comments below, I added the UpdateTarget function in the source, which is called from the edit * trigger:

 function UpdateTarget() { try { var ss = SpreadsheetApp.openById("targetID"); } catch(err) { Browser.msgBox(err); } var sheet = ss.getSheetByName("Weekly"); sheet.getRange("A4").setValue("=query(ImportRange("sourceID", "Weekly!B:BI"), CONCATENATE("select * WHERE Col1='",B3,"'"), 0) "); SpreadsheetApp.flush(); } 

This works more efficiently, but I can also have a script to write updated values ​​directly to the target if I go along this route.

I really wanted Target to be a template that other users could duplicate, and that would extract certain "live" data from the main source, I do not want to implement scripts in the source that need support when adding new targets.

I think that I really need to reformulate the formula so that it depends on the cell, which can be edited and cause a recount, but does not affect the result?

(* Aside: I found that the openByID function returns an “Action is not allowed” exception if it is called through an explicit trigger - onEdit is prohibited for editing other spreadsheets - see https://developers.google.com/apps-script / understanding_triggers ).

Answer 1

Formulas are recalculated when the spreadsheet is opened by the user or when the cell (inside the same table) changes, which affects the formula.

Since you are working with two different spreadsheets, the latter case is not applicable.

Using the table, you call the flush () function. I assume it is in the target table. It will be useless.

One solution would be to write a script in the source spreadsheet that will modify the target table each time the source is edited

Answer 2

Force recalculation of import variables by restoring cell formula

It is required:

Imported data is your goal where everything will be copied.

Imported data configuration - contains the following fields:

  + --------------------------------------------- + --- ----------------------------- +
 |  A |  B |
 + --------------------------------------------- + --- ----------------------------- +
 |  Import Data from Spreadsheet with the key: |  key |
 |  Import Data from Spreadsheet between range: |  A: AA |
 |  Imported Data select columns: |  SELECT * |
 |  Imported Data criteria: |  WHERE Col15 contains 'Offered' |
 |  Imported Data should by ordered by column: |  ORDER BY Col1 |
 + --------------------------------------------- + --- ----------------------------- +

Script:

 function onOpen() { try { var ss = SpreadsheetApp.getActiveSpreadsheet(); } catch(err) { Browser.msgBox(err); } var configsheet = ss.getSheetByName("Imported Data Config"); var configkey = configsheet.getRange("B1").getValue(); var configrange = configsheet.getRange("B2").getValue(); var configselect = configsheet.getRange("B3").getValue(); var configwhere = configsheet.getRange("B4").getValue(); var configorderby = configsheet.getRange("B5").getValue(); var importedsheet = ss.getSheetByName("Imported Data"); importedsheet.getRange("A1").setValue('=QUERY(IMPORTRANGE("' + configkey + '","' + configrange + '"),"' + configselect + ' ' + configwhere + ' ' + configorderby + '")'); SpreadsheetApp.flush(); // Solution of sourcecode is: http://stackoverflow.com/questions/13631584/how-can-i-force-a-recalculation-of-cell-using-importrange-function-in-a-google-s // OnOpen Trigger: https://developers.google.com/apps-script/understanding_triggers // Active Spreadsheet solution: https://productforums.google.com/forum/#!topic/docs/XIY0WNX0uL8 Browser.msgBox("Sync Complete!"); } 

This allows you to change the formula without editing the script and simplify the transfer of the script to different sheets.

Answer 3

I also had this problem. I need to import only one cell, which is in a constant place.

What I did to solve this problem for myself, the following function was used, initiated by onEdit on the target sheet.

 function Refresh() { var ss = SpreadsheetApp.openById('*yourtargetsheetID*'); var sheet = ss.getSheetByName('*yourtargetsheetname*'); sheet.getRange('*locationofyourimportrangeformula*').setFormula('*yourimportrangeformula*'); }