https://fabform.io/a/how-to-find-google-sheet-id
On Wednesday, October 21, 2020 at 10:34:43 PM UTC+1 Pedro Reis wrote: > Just a note that I need both values (formulas) and cell color (done by > conditional formatting) processing done. > > On Wednesday, October 21, 2020 at 10:24:48 PM UTC+1 Pedro Reis wrote: > >> Hello! >> >> I also need to (on a weekly base) add some values to a spreadsheet, >> ask for it to be processed (as if I had the spreadsheet open in a browser >> , so we get the grey progress bar) >> wait that the process gets completed and copy result to another >> spreadsheet (that contains historic data). >> >> So for me the *Recalculation* can be just *On change* . >> >> What API commands can I use? >> >> >> On Wednesday, January 25, 2017 at 3:50:38 PM UTC [email protected] >> wrote: >> >>> Thanks for the additional detail James, and I'm totally with you as far >>> as spreadsheets being an awesome nexus for data. It's worth noting that if >>> you push data into the spreadsheet, instead of pull it, then recalculation >>> wouldn't be an issue. The use of non-deterministic formulas, like NOW(), >>> GOOGLEFINANCE(), transform a spreadsheet from a database to a something >>> more like a program or script, and that's where recalculation challenges >>> come in. >>> >>> That said, users are very likely to continue to use these >>> non-deterministic formulas and we should make sure that Sheets API users >>> have a reasonable way of working with them. >>> >>> - Eric >>> >>> On Tue, Jan 24, 2017 at 11:44 PM, James Dobson <[email protected]> >>> wrote: >>> >>>> Thanks for the quick response! >>>> >>>> >>>> > Recalculating a spreadsheet uses some computational resources, so we >>>> probably will never be at a point where we recalculate on every API >>>> request. Additionally, recalculation can take some time and we don't want >>>> to block the API request until that is complete. >>>> >>>> Understood. I wouldn't actually want it to recalculate on every API >>>> call, either. Control over whether or not a recalculation happens on a >>>> particular call (e.g. via an HTTP caching header or just a flag in the >>>> query params), or how often recalculations happen (e.g. every x minutes >>>> for >>>> spreadsheet y, every z requests, etc.), would be sufficient. >>>> >>>> Even without control, just having better predictability around how >>>> often recalculations happen would be great. Right now, it's so >>>> unpredictable, I never know if I'm going to get a recent value or a value >>>> that is hours old. Knowing that recalculations are guaranteed to happen in >>>> any Sheets document in the worst case once every x minutes would at least >>>> allow me to select an appropriate polling interval. >>>> >>>> >>>> > For context, can you provide some more information about your use >>>> case? Understanding how you use the API will help ensure we build the >>>> right >>>> solutions. >>>> >>>> As an example, right now I'm trying to take some information that a >>>> spreadsheet computes about my investments and surface it on my phone (and >>>> eventually wristwatch). For example, maybe I'll track my net worth or >>>> spending for a few months. If one wants a metric to move in a specific >>>> direction, one should start by being aware of the value of that metric, >>>> right? ;) >>>> >>>> Eventually, I'd like to take other metrics (e.g. new customers today, >>>> bugs squashed this week, Amazon sales this month, etc.) and display them >>>> conveniently, too. Some of this information would already be in >>>> spreadsheets, and some of it is external and needs to be pushed/pulled >>>> from >>>> other systems. >>>> >>>> Folks will argue that we should be writing scripts to do this, but >>>> there are good reasons to do it via spreadsheets. First, not everyone can >>>> write such a script, and many of those who can would probably end up >>>> making >>>> a spreadsheet anyway. Second, scripts like this require some degree of >>>> operationalization, whereas Sheets handles that for you (it's already Ops >>>> for running your business). Third, a single integration point like Sheets >>>> requires fewer integration connections than a point-to-point model. >>>> Fourth, >>>> spreadsheets make it easy to combine manually-entered data with live data. >>>> Last, the data from other systems are just that: data. People use >>>> spreadsheets to give those data meaning (or spin for corporate >>>> politics...). >>>> >>>> So I believe that there's a lot of information that is trapped inside >>>> spreadsheets that people rarely see because they have to open up the >>>> sheets >>>> to see it; it's just too many clicks for someone who doesn't live in >>>> spreadsheets all day. With its API and the live nature of its documents, >>>> Sheets will let me surface that information so that more people see it, >>>> more often. >>>> >>>> Well... enough proselytizing for today. I hope that gives you an idea >>>> of how I want to use the API, >>>> >>>> James >>>> >>>> >>>> >>>> On Tuesday, January 24, 2017 at 11:49:19 AM UTC-5, Eric Koleda wrote: >>>>> >>>>> Hi James, >>>>> >>>>> Thanks for the detailed explanation of the behavior you are seeing. >>>>> Recalculating a spreadsheet uses some computational resources, so we >>>>> probably will never be at a point where we recalculate on every API >>>>> request. Additionally, recalculation can take some time and we don't want >>>>> to block the API request until that is complete. I've started a dialog >>>>> with >>>>> the engineering team to see if there are any changes we can make to the >>>>> API >>>>> to make it easier to deal with recalculation. >>>>> >>>>> For context, can you provide some more information about your use >>>>> case? Understanding how you use the API will help ensure we build the >>>>> right >>>>> solutions. >>>>> >>>>> Thanks, >>>>> - Eric >>>>> >>>>> On Monday, January 23, 2017 at 10:22:52 PM UTC-5, James Dobson wrote: >>>>>> >>>>>> Hi, >>>>>> >>>>>> I have a sheet with a value is computed from external data, so it is >>>>>> constantly changing. This works well when running Sheets in a browser >>>>>> window. But I’m observing that the value returned by >>>>>> *sheets.spreadsheets.values.get* is often stale. I’m hoping that >>>>>> someone reading this forum can provide some insight as to what’s going >>>>>> on >>>>>> and how I can get fresh data via the API. >>>>>> >>>>>> Here's a simple example: >>>>>> >>>>>> - Create a spreadsheet with the formula *=NOW()* in cell *A1* >>>>>> - In *File* > *Spreadsheet settings…* choose the *Calculation* >>>>>> tab and ensure *Recalculation* is set to *On change and every >>>>>> minute*. >>>>>> - Use the Google APIs Explorer to perform *sheets.spreadsheets.va >>>>>> <http://sheets.spreadsheets.va>lues.get* to get the value of that >>>>>> cell (*range* parameter is typically *Sheet1!A1*) >>>>>> - I’d expect every time I press the *Authorize and execute* >>>>>> button, I’d get a new value for the cell. >>>>>> - What actually happens is that I typically get an old value. >>>>>> >>>>>> >>>>>> I wrote a Python script to poll the API every minute. The median time >>>>>> elapsed before a new value appears is about 70 minutes, but I have seen >>>>>> as >>>>>> high as 1006 minutes, and as low as 1 minute. >>>>>> >>>>>> When the sheet is loaded in a browser window, the cell gets updated >>>>>> every minute as expected. Using the browser to edit another cell causes >>>>>> the >>>>>> API to return the most recent version of the computed cell. >>>>>> >>>>>> It seems that the computation of formulae is a complex process that >>>>>> can execute entirely on the server, entirely on the browser, or in >>>>>> combination: >>>>>> >>>>>> - When the formula is *=NOW()*, there seems to be zero >>>>>> communication between the browser and the server when the formula is >>>>>> recomputed. >>>>>> - When the formula is *=GOOGLEFINANCE("GOOG")*, it seems that the >>>>>> server sends a push notification to the browser about new external >>>>>> data >>>>>> being available, the browser retrieves the external data from the >>>>>> server, >>>>>> and then any dependent cells are computed entirely in the browser. >>>>>> >>>>>> >>>>>> What I’d like to do is be able to force the back-end to recompute the >>>>>> formula when the get API is called. Does anybody know how to do this? >>>>>> >>>>>> The best hack I have is to *get* the formula from the cell using >>>>>> *valueRenderOption=FORMULA*, and then *update* the same formula back >>>>>> to that cell with *valueInputOption=USER_ENTERED* and >>>>>> *responseValueRenderOption=FORMATTED_VALUE*. This forces the server >>>>>> to recompute the formula and returns the result in the response to the >>>>>> *update* call. It also doesn’t seem to pollute the history of the >>>>>> document too much. Unfortunately, this approach creates a race condition >>>>>> that I'd rather avoid. >>>>>> >>>>>> Thanks, >>>>>> >>>>>> James >>>>>> >>>>>> -- >>>> You received this message because you are subscribed to a topic in the >>>> Google Groups "Google Spreadsheets API" group. >>>> To unsubscribe from this topic, visit >>>> https://groups.google.com/d/topic/google-spreadsheets-api/D6Sg397grzo/unsubscribe >>>> . >>>> To unsubscribe from this group and all its topics, send an email to >>>> [email protected]. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/google-spreadsheets-api/4978a826-08ff-4995-be4a-b4bb50b316b6n%40googlegroups.com.
