How to refresh a sheet's cell value in Google

问题:

I'm experimenting with Blockspring which provides a Google Sheets add-on which can, for example, run a function which returns data from a webservice e.g.

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

I want to refresh a cell's data but don't see a 'refresh' call in the docs.

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

}

回答1:

Use the flush() method:

Google Documentation

SpreadsheetApp.flush();

Quote from the documentation:

Applies all pending Spreadsheet changes

If there were no changes to the spreadsheet, but you want to force formulas to recalculate, you will need to make a change, and then use SpreadsheetApp.flush();

For example, you could get the value from cell A1, then set the same value to cell A1. So, there is no chance of losing data because you are getting and setting the same value. The change allows. SpreadsheetApp.flush(); to recalculate all the formulas.

回答2:

Example based on @Sandy_Good answer above. Adding since I had to struggle a little to interpret how to use this to auto eval a cell formula on any sheet change. This code runs slow, but I'm sharing it as a general example in hopes it helps, 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 will temporarily re-write the formula in cell 1,1 replacing "=" with "?", then flush(), then change it back, forcing the evaluation after any edit in the sheet.

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

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

回答3:

Refresh all table Its slow, but working!

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

回答4:

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

Hope that helps