How can I make an Import function run without opening the sheet?-CodePudding

I have a Google Sheet that uses an IMPORTRANGE query to combine data from multiple other sheets. This combined import sheet is read by Google AppSheet. We have realized that the data AppSheet is reading is always outdated. It only reads the data as of the last time the sheet was manually opened.

I followed the steps in

when A1 is unchecked, the result will be empty, then check A1 to fill once again the result as expected

solution #2

by script, try for instance

function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet9')
  var data = SpreadsheetApp.openById('1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI').getSheets()[0].getDataRange().getValues()
  sh.getRange(1,1,data.length,data[0].length).setValues(data)
}

put a daily triger as needed

CodePudding user response：

SpreadsheetApp.flush() only works for the script execution that calls it. If you need to refresh the data results from a formula it's uncertain how exactly the spreadsheet will respond as most of the formula calculations are done on the client side. You could verify this by yourself by using your web browser developer tools.

Anyway, spreadsheet formulas have several caveats so it will not be extrange that at some point you will have to rethink your solution. Assuming that you want to keep using AppSheet:

Use AppSheet for your front end and some no-code / low-code automation. Keep your app small, if you need many forms / views consider to distribute them among several apps.
Use Google Sheets only for data storage for your AppSheet app. Please bear in mind that it has 10 million cells limit for the whole spreadsheet, so you might want to delete the unused sheets and delete the unused columns and rows on each sheet.
You might use Google Apps Script to do the data import and transformation tasks. If you need that something be updated based on actions done on the AppSheet app, you might use an installable change trigger or use webhook from the AppSheet side to and a "simple" web application using Google Apps Script (you could use GET / POST http requests to trigger some Google Apps Script functions).

Also you might use other programming platforms for the data import / transformation tasks and keep using Google Sheets as your AppSheet database by using the Google Sheets API or other automation tools like Zappier, IFTTT, Integromat among many others.