Google-sheets – Can a Google Spreadsheet be recalculated if it’s not manually opened

google sheetsgoogle-apps-scriptimporthtml

We have some Google Spreadsheets that use the ImportHtml() to pull in some real messy tables. Then on on an alternate page we access the above data and pull it into a nice cleanly formatted table via spreadsheet QUERY() and other functions so that subsequently a batch process from our database can import it.

Our problem is that we need the Google Spreadsheets to refresh and populate without manually opening them because if not, the database batch import fails.

We know that there are probably more robust ways to do this but it makes it easy enough for our less tech proficient people to do because it liberates them to build these type of data tools themselves.

From what we've read, running SpreadsheetApp.flush() via a G-Apps Script might not force all the pages to recalculate.

Is there another way to easily do it?

Best Answer

If you are using the new (Q2 2014) spreadsheets you can try the Recalculation option found under [File] » [Spreadsheet Settings ...]

I believe it will work in your case. It depends what you mean by "on an alternate page". If you mean a 2nd sheet in the same spreadsheet you may be in luck. If you mean an alternate workbook, I think you lose.

In our case, we have several users with workbooks having ImportRange() calls to a master workbook, which is updated by Google Apps Script activity. The new Recalculation feature does NOT cause the remote users' workbooks to pull from the master the way a manual edit would.

Related Solutions

Google Sheets – ImportHtml() with Basic Auth

ImportHTML does not support authentication. The data has to be publicly available for you to use this function.

This applies to ImportXML & ImportFeed also.

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

This little script will:

create an extra menu item, thus making it accessible throughout the workbook. T
the menu item will set the active range to column E, last row + 1.

See the example I've prepared: Creating a function...Google spreadsheet is opened (edit rights)

function onOpen() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // create menu
  var menu = [{name: "get last row", functionName: "getLastRow"}];

  // add to menu
  ss.addMenu("Rows", menu);  

  // execute function
  getLastRow();  
}

function getLastRow() {
  // get active spreadsheet
  var ss = SpreadsheetApp.getActive();

  // get active sheet
  var sh = ss.getActiveSheet();

  // get column F
  var indLastCol = sh.getLastColumn()-1;

  // get last row + 1
  var indRowWithout = sh.getLastRow()+1;

  // set new active range
  sh.setActiveCell('E'+indRowWithout);
}

Best Answer

Related Solutions

Google Sheets – ImportHtml() with Basic Auth

Google-sheets – Creating a function that runs when a Google spreadsheet is opened

Related Topic