CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
gfunkdave
gfunkdave

Reputation: 73

How do you force formula recalculation in Google Sheets apps script?

I've got a Google Sheet that uses the INDIRECT() function to sum up values from other sheets that don't yet exist (they are created by the user with an app script macro). The issue is that Google doesn't refresh the INDIRECTs when the user creates the sheets referenced by them, so the sum cell remains empty even though the sheets referenced by INDIRECT now exist. It's worth noting that Excel auto-refreshes in this case and displays the sum correctly.

More detail: this is a golf league spreadsheet. Users choose a match date from a dropdown and note who's playing, then click a Generate Scorecard button that creates a new tab with the details of that match. The new tab name is the host club and match date. The idea is that as they create scorecards they will accumulate a new tab for each match, and I use INDIRECT to sum up the scores for all matches for each player.

I've tried using SpreadsheetApp.flush() to refresh the spreadsheet but it doesn't do anything. It seems the only way to get it to calculate is to cut and paste the formula back in to the same cell.

How do you make Google recalculate and re-evaluate the INDIRECTs in the spreadsheet?

Upvotes: 1

Views: 963

Answers (1)

gfunkdave
gfunkdave

Reputation: 73

I wound up adding a

myRange.copyTo(myRange);

after creating the new tab, which forced the INDIRECTs in myRange to recalculate.

Upvotes: 3

Related Questions