cellvalue is ok on Gsheet cell, but empty when retrieved by getRange via googleapp

[Kevin GUISELIN]

Hello,

I have a formula based on index equiv to get a data according to conditions.
The value is well displayed in the googleSheet cell (example: 2). However, when I want to retrieve it via a getRange() under appscript, getRange only retrieves an empty element and not the 2.

I noticed that when opening the googlesheet, there is a small loading delay before the 2 appears. I run the script a few minutes later, but nothing happens, the retrieved element remains empty.

Have you ever seen this problem?

Thanks

[cbmserv...@gmail.com]

Hi Kevin,

 

Not sure what your script is doing. Share your script so we can give you some advice.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f03bc297-c7ef-4fc7-8f68-65f662428dffn%40googlegroups.com.

[Kevin GUISELIN]

Hi George,

thanks for your answer.

I finally found the problem this morning. Script and formula were ok. The problem was coming from the importRange values that I used with the formula.

I already noticed this, sometimes the values of the importRange column are visible on gsheet, but when you do a Logger.log with appScript, values are only #N/A errors or juste empty values.

But the strange thing is the formula INDEX/EQUIV with the importRange values are working well, as if values are stocked in the browser cache maybe ?

So values exist with gsheet formula, but are inexistant with Logger.log appscript at the same time :). I re-loaded the importRange column then the problem disappeared. 

kevin

[cwl...@gmail.com]

It could also be getting the value during the calculation. In that case, in your script, add a  SpreadsheetApp.flush() right before you retrieve the cell value.  This forces all calculations to finish without visibly updating the sheet.

[Kevin GUISELIN]

Hi,

thanks.

did it too yesterday, but pb were still there.

importRange was really the source of the pb

[CBMServices Web]

There are several ways to get the values of cells in the spreadsheet. You can use getValues or getDisplayValues of getFormulas and others.. each will return a different value dependent on what you ask for.

Glad to hear you solved your issue.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/228fcc88-1d25-4051-b824-1cd0cb699eb1n%40googlegroups.com.