How to update worksheet cell value in Google Apps Script

Question

I'm experimenting with Blockspring , which contains a Google Sheets add-in that can, for example, run a function that returns data from a web service, for example

=BLOCKSPRING("get-stock-current-stats", "ticker", "MSFT") 

I want to update the cell data, but do not see the "update" in the documents.

 function onOpen() { createTimeDrivenTriggers() } function createTimeDrivenTriggers() { // Trigger every minute ScriptApp.newTrigger('myFunction') .timeBased() .everyMinutes(1) .create(); } function myFunction() { Logger.log('I ran'); // can see this in the logs SpreadsheetApp.getActiveSheet().getRange('B4').getValue() //presumably returns a value to the script } 

Answer 1

Use the flush() method:

Google documentation

 SpreadsheetApp.flush(); 

Quote from the documentation:

Applies all pending table changes.

If no changes have been made to the table, but you want to force the recalculation of the formulas, you will need to make changes, and then use SpreadsheetApp.flush();

For example, you can get a value from cell A1, and then set the same value to cell A1. Thus, there is no way to lose data, because you get and set the same value. Change allows. SpreadsheetApp.flush(); recount all formulas.

Answer 2

Example based on @Sandy_Good answer above. In addition, I had to work a bit to understand how to use this to automatically evaluate the cell formula for any change in the sheet. This code is slow, but I will share it as a general example in the hope that this will help, YMMV.

 /* Force evaluation of custom formula on sheet change. * Temporarily changes formula text. * @param sheetName: String Needs to be present in the formula * @param row: Int * @param col: Int */ function forceEval(sheetName, row, col){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName(sheetName); var orig = sheet.getRange(row,col).getFormula(); var temp = orig.replace("=", "?"); sheet.getRange(row,col).setFormula(temp); SpreadsheetApp.flush(); sheet.getRange(row,col).setFormula(orig); } 

Now call it using the onEdit trigger.

 function onEdit(e){ forceEval("MySheet", 1, 1) } 

This temporarily rewrites the formula in cell 1.1, replacing "=" with "?", Then flush (), then brings it back, which will result in an evaluation after any editing on the worksheet.

A custom formula in a cell might look something like this: = SUMSTATUS ("MySheet", "InProgress")

Should work for: = BLOCKSPRING ("get-stock-current-stats", "ticker", "MSFT")

Answer 3

Refresh the entire table. It is slow, but it works!

 var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var rangeData = sheet.getDataRange(); var lastColumn = 38; var lastRow = 17; var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1); function forceRefresh() { //Loop through each column and each row in the sheet. for(i = 1; i < lastColumn; i++){ for (j = 1; j < lastRow ; j++){ var cell = searchRange.getCell(j,i); var formula = cell.getFormula(); var tempFormula = formula.replace("=", "?"); cell.setFormula(tempFormula); SpreadsheetApp.flush(); cell.setFormula(formula); }; }; }; forceRefresh(); //call 

Answer 4

Select a cell with a formula or several cells with formulas From the Google menu, select: Add-ons > Blockspring > Refresh Selected Cells

Hope that helps