0

I have a google sheet where there are some functions in the cell like:

    =CLEAN(SUBSTITUTE(CONCATENATE("https:",scrape(A1)),"https:",))
 =CLEAN(SUBSTITUTE(CONCATENATE("https:",scrape(B1)),"https:",))

and behind the scenes I have script function running that pulls the output of the cells (after scrape function runs) and pushes the value to a 3rd party database via API.

I have the script functions running periodically by this approach:

ScriptApp.newTrigger('API_Data_push_function')
.timeBased()
.everyHours(24)
.create();

but I have noticed in the 3rd party database that values are coming like 

#NAME?

But when I open the google sheet and run the script function manually, it runs fine.

So, I am trying to understand if google sheet UI needs to be opened for cell functions to run properly? OR if the issue is the API data push function is running faster than cell function which is returning #NAME? value.

Can anyone confirm if google sheet needs to be opened for cell functions to work? if yes, any way for script to force cell function to run first prior to script to start running?

kuml
  • 231
  • 5
  • 15
  • *Can anyone confirm if google sheet needs to be opened for cell functions to work?* yes. Why do you want to use cell functions, when you don't want to open the sheet? – TheMaster Jun 05 '20 at 17:16
  • Hi @TheMaster - Just to be confirm, are you saying google sheet needs to be opened in order to get the functions run correctly? As per Cooper before, it does not need to be opened. Is there any help doc that i can use to confirm this info? Regarding why i want to use function over script.. I guess, it is easier to add functions to the google sheet UI than in the code to my knowledge. Please correct me if I am wrong. In a cell, i just need to add the function while in the code, i need to define sheet, getcell range for each function then go setvalue for each cell where i want the results. – kuml Jun 05 '20 at 17:35
  • Hi @TheMaster I just wanted to confirm if my above questions / comment made sense? Please let me know if it does not and I am happy to explain in a different manner. I am really looking for a confirmation if google sheet cell functions requies sheet to be opened or not. I have worked on some past sheets where one sheet is getting value from another sheet and those another sheets are using cell functions and it always worked. But Now that I am having issues, I am started to question if cell functions always work or not without opening the google sheet. – kuml Jun 06 '20 at 01:08
  • AFAIK, It doesn't. https://stackoverflow.com/questions/49268415 Usually the data is cached. This is especially true in case of import functions and probably custom functions. It's not hard to test this theory. Create a new sheet> create a dummy custom function> create a dummy trigger function to log the data to stackdriver. If it shows `#NAME?`, then you've answered your question. – TheMaster Jun 06 '20 at 10:57
  • As to your comment about easiness of Google sheet functions, it depends on your knowledge of scripts. It's a good idea not to mix sheet functions with scripts. As to how, you'd generally use batch operations-> So, `getValue` "**s**" and `setValue` "**s**". See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for best practices. – TheMaster Jun 06 '20 at 11:02
  • Hi @TheMaster first of all, thank you your response. Two questions- (1) please let me know if my previous comments about why I'm using cell functions over script makes sense or if I'm incorrect? (2) regarding dummy trigger and logging data to stack driver, are you referring to if I should test all very formulas that I'm using? Any chance you could give me an example how to accomplish this / create the dummy flow? I'm still new to this. Thanks – kuml Jun 06 '20 at 11:05
  • Hi @TheMaster regarding the point that it is a bad practice or not a good idea to mix sheet functions with scripts, is there any documentation to support that? I'm really interested in learning in why it is not a good approach. It'll help me in learning things correctly – kuml Jun 06 '20 at 11:09
  • About (2), Just tested my theory. Custom functions does seem to work even if the sheet is not opened. Preliminary results show that I might be wrong. About (1), not really. It's better to do it in scripts or sheets, but not in both. You might come across issues like these where the data is not synced properly. [This](http://productforums.google.com/forum/#!topic/docs/vW1FfLZuqCA) might suggest that. As far as your question is concerned, We need more info. Does all values come as NAME or only after a certain time after the sheet is opened? Any error logs? How is A1 and B1 changed? – TheMaster Jun 06 '20 at 11:22
  • I know for a fact, too many custom functions might result in `#NAME`. I believe using arrays will rectify your problem: https://developers.google.com/apps-script/guides/sheets/functions#optimization – TheMaster Jun 06 '20 at 11:24
  • Do you mind sharing the test you performed to test the theory? – kuml Jun 06 '20 at 11:45
  • Does that mean ALL formulas in cell should produce output even if sheet is not opened? – kuml Jun 06 '20 at 11:50
  • `const dummyCf = (arg) => arg; const dummyTrigger = ()=>console.log(SpreadsheetApp.getActive().getRange("A1").getValue()); const changeB1=()=>SpreadsheetApp.getActive().getRange("B1").setValue(3)` In first sheet!A1: `=dummyCf(B1)`. Close the sheet. Create a minute trigger for `dummyTrigger`. See logs> Whatever was in B1 will be logged. Manually run `changeB1`. 3 will be logged instead of whatever was in B1 before. – TheMaster Jun 06 '20 at 12:02
  • *Does that mean ALL formulas in cell should produce output even if sheet is not opened?* That should be case for all functions. But import* functions and custom functions that take longer might be unreliable. – TheMaster Jun 06 '20 at 12:06
  • Gotcha, thank you for your guidance and answering my questions. – kuml Jun 06 '20 at 12:33
  • I'm struggling with another request where I need some help with the code. I would appreciate if you could please provide some feedback? https://stackoverflow.com/questions/62221236/checking-if-a-sheet-cells-includes-errors-or-not/ – kuml Jun 06 '20 at 12:35
  • Hi @TheMaster do you mind sharing your sheet so I can view the test you performed? I am having hard time in understand how can I perform test on various functions. If in your test sheet you could show me a couple of cell formulas and testing it, that would be appreciated..Thanks! – kuml Jun 06 '20 at 21:04
  • I won't share a Google sheet because it'll expose my email. I gave you step by step instructions. Which step are you having trouble with? There's only 1 cell function in Sheet1!A1 : =dummycf(B1) – TheMaster Jun 07 '20 at 07:44
  • Hi @TheMaster what needs to do in cell B1? Looking at the code, are you setting the value of cell B1 to 3? so A1 cell should show 3 as well? I am just trying to understand how i can use your test to perform a test on various custom functions so i can see how custom functions perform when sheet is not opened. – kuml Jun 08 '20 at 14:30
  • Yes and yes. B1 can initially show something else like, 1. Close the sheet. Change B1 to 3 using script. See logs of the triggered `dummyTrigger` function, which should be set to run every minute. – TheMaster Jun 08 '20 at 15:33
  • Sounds good. Thanks @TheMaster One request. I am expanding this testing to test what might be failing during the automated run for the sheet and I am noticing this error in the execution log. Do you know how to resolve this? This might be the actual issue why my sheet is not running when it is not opened. `Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request` I did some researched and made sure the URL exists in scope list (under propertied) and in manifest json with authorization. still getting error – kuml Jun 08 '20 at 15:59
  • The error is indeed strange, but need context of execution to confirm suspicions. Better to ask a new question with [mcve]. – TheMaster Jun 09 '20 at 09:03

1 Answers1

3

No...But you might need to use SpreadsheetApp.flush() in your scripts to make sure that when you read values off of the spreadsheet after intermediate operations that your getting the correct values.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Hi @Cooper thanks for confirming that google sheet does not need to be opened for cell functions to work. yes i am using flush still getting #NAME? when i let the sheet to run automatically on timer but when i manually run script, it works. Just to confirm what is the right placement for flush? – kuml Jun 05 '20 at 16:31
  • Thank you @Cooper one quick question, is there any code / function that i can add to my script to check if any cell has an error then do nothing, if no then only trigger my function? I am not sure what is the best way to check errors in a sheet via script. – kuml Jun 05 '20 at 17:22
  • Hi @Cooper I just wanted to clarify if google sheet does not need to be opened for cell functions to work. Any chance you would confirm what TheMaster user is referring to in the above comments? – kuml Jun 06 '20 at 01:42
  • @Cooper I thought it was a bound script and not a standalone script requiring openById(). It does work, when the script is bound. – TheMaster Jun 06 '20 at 12:07