I am writing a simple script that basically reads a value and send it to another sheet upon clicking a button. For testing purpose I wrote:

SpreadsheetApp.flush()
var sheet = localSheet.getSheetByName('Input');
var dateTimeRange = sheet.getRange(2,2);
var dateTimeValue = dateTimeRange.getDisplayValue();  
localSheet.toast(readingValue, dataTypeValue)

In my spreadsheet setting, I set: File->Spreadsheet settings->Calculation->On change and every minute

Cell in range 2,2 has

=now()

So, every minute, the value in cell 2,2 recalculates itself. But when I run the script, the output value that I see is always the value of the last time I modified the sheet. As long as I don't interact with the sheet, the script is unable to read the new value. I tried adding flush(), doesn't work. Anyone have any idea?

Thank you.

1 Answers

0
Tanaike On

I think that the reason of your issue is that the sheet is not updated. In your case, unfortunately, flush() cannot be used. In order to avoid this issue, how about the following workarounds? Please think of this as just one of several answers.

In your situation, when the sheet is edited, the value retrieving by getDisplayValue() is updated. In this answer, this is used.

Workaround 1:

In this workaround, the cell "B2" is overwritten by =NOW().

Modified script:

var localSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = localSheet.getSheetByName('Input');
var dateTimeRange = sheet.getRange(2, 2);
var dateTimeValue = dateTimeRange.setFormula("=NOW()").getDisplayValue(); // Modified
localSheet.toast(dateTimeValue);

Workaround 2:

In this workaround, an empty cell is cleared. At sample script, the cell "B3" supposes the empty and this cell is not related to the main data. This cell is cleared. By this, let Spreadsheet know the sheet is edited.

Modified script:

var localSheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = localSheet.getSheetByName('Input');
var dateTimeRange = sheet.getRange(2, 2);
sheet.getRange("B3").clear(); // Added
var dateTimeValue = dateTimeRange.getDisplayValue();
localSheet.toast(dateTimeValue);
  • Value of "B2" on the sheet "Input" is updated even if the edited cell is different from the main sheet. So you can also use localSheet.getSheetByName("Sheet1").getRange("A1").clear() instead of sheet.getRange("B3").clear().

Note:

  • It seems that your script might not be the latest one.
    • Is localSheet var localSheet = SpreadsheetApp.getActiveSpreadsheet()?
    • readingValue and dataTypeValue are not declared. In this answer, dateTimeValue is used.
  • In above workarounds, the value is updated. But the retrieved value shows the value of 4 or 5 seconds faster every time. About this, I couldn't find to do the same value with "B2". I apologize for this situation.

If this was not useful for your situation, I apologize.