Run formula calculation in Google Sheets without to open file

0
votes

I have a scheduled addon, which writes data periodically into a Google Sheet, without to open file. I have a formula, which should make some calculations on written data and write calculated values into the sheet, so then Data Studio with this sheet as data source updates the visualization.

What is the way to periodically run a formula calculation and write calculated values without manual steps like opening file?

I'm pretty new to this topic: my search approaches to find something like convert formula to app script or schedule formula execution brought me not to any fruitful idea.

PS_ the formula, which should do the calculation is:

=IFERROR(((VLOOKUP(A2,'[1]SV'!$A:$B,2.0))/B2)+(IF(B2=1,"33,9",IF(B2=2,"16,28",IF(B2=3,"10,36",IF(B2=4,"7",IF(B2=5,"5,64")))))+IF(B2=6,"4,13",IF(B2=7,"3,27",IF(B2=8,"2,61",IF(B2=9,"2,18",IF(B2=10,"1,82")))))+IF(B2=11,"1,77",IF(B2=12,"1,81",IF(B2=13,"1,85",IF(B2=14,"1,9",IF(B2=15,"2,04")))))+IF(B2=16,"1,68",IF(B2=17,"1,61",IF(B2=18,"1,65",IF(B2=19,"1,62",IF(B2=20,"1,59","0")))))),0)

If i would know, how to convert the formula to app script, i would manage the rest - i'm familiar with running scripts with time based trigger. Or, maybe, there is a method to run formula on the same scheduled way, like scripts...?

2
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén
I also have a question for you, so far how have you been deploying your project? – Baby_Boy
@Baby_Boy currently it is deployed as a Google Sheet with a scheduled addon. With this addon i write into the sheet some JSON data from a remote API. The next steps are:to make a calculation and to connect the sheet to Datastudio for visualization. – Evgeniy
Regarding convert the formula to Apps Script you should post a new question and include on it the respective description of your search/research efforts. Related Implementing vlookup and match in function – Rubén
What about dummy placement of NOW() function in the formula (like IF(ISDATE(NOW()), <code>, "")), that is recalculated on time trigger and so forces the formula to be recalculated too? – astentx

2 Answers

2
votes

*Disclaimer: I have not tried this and it might not work, if it doesn't, comment and I will try to provide another solution

OK here's what i would try: In google scripts there is a trigger function, manly used for testing. what it does is it runs your script and sends you an email if there is a error, and hypothetically, you could use this to run you script periodically.

A couple of other things I may try if this doesn't work, leave a comment if you would like me to go in depth about these:

depending on how you want this to work, you could set up a web app to do this, and instead of opining google sheets open the web app with a timer built into the script, that would trigger as long as you had the web page opened.

It might also be possible to run the script automatically with a JavaScript function, requires more research.

1
votes

If Google Data Studio is not showing you the correct value then you could use a time-driven trigger to save the formula results in another cell. Example:

function respondToTimeDrivenTrigger(e){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  /** The range holding the formula */
  var range = spreadsheet.getRange('Sheet1!A1');

  /** The value returned by the formula */
  var value = range.getValue();

  /** Write the value to the cell to the right or the source cell */
  range.offset(0,1).setValue(value);
}

Resources