I am currently trying to embed a Google Spreadsheet chart into a Google Slide that automatically updates on the slide whenever the chart changes on spreadsheet side. (It's basically a timer that counts down towards the end of the year.)

The crux is that the update on slide side only works successfully if the timestamp for "last updated" in the sheet differs from the timestamp of the last update. However, in the spreadsheet, the chart is updated by a formula that calculates time differences. So while the value changes constantly, the spreadsheet itself does not change as the formula does not change. Thus, the timestamp does not get updated even if the chart changes.

So I was wondering if there is a way to tell Google Spreadsheet to update the "last changed" timestamp on a regular basis so I can trigger the update on Slide side. Does anyone has an idea how to achieve that?

CodePudding user response：

Create a time-driven trigger to run a short function periodically, say once an hour. The function should update some cell in the spreadsheet. Try this:

function updateTimestamp() {
  const cell = SpreadsheetApp.getActive().getRange('Sheet1!A1');
  cell.setValue(new Date());
}

Format the cell as Format > Number > Date time.