有没有一种方法可以在执行batchGet之前等待Google表格完成计算? [英] Is there a way to wait for Google sheets to finish calculating before performing batchGet?

问题描述

我目前正在使用Python google-api-python-client访问Sheets API v4，以便阅读&将数据写入工作表.

I am currently using the Python google-api-python-client to access Sheets API v4 in order to read & write data to a worksheet.

此工作表将产生40行数据，所有数据都严重依赖于多次迭代计算.通过API写入数据可以按预期工作，但是，似乎在表完成计算之前，调用batchGet会从表中读取值.

This worksheet results in 40 rows of data all of which heavily rely on multiple iterative calculations. Writing the data via API works as intended, however, it appears that calling batchGet reads values from the sheet prior to the sheet finishing it's calculation.

这将导致读取的值根本不正确且不准确.有什么方法可以确保工作表在读取数据之前完成计算?

This results in read values that are simply incorrect and inaccurate. Is there any way to ensure that the worksheet finishes calculating before reading the data?

推荐答案

gSheet API不会提前返回"，但是工作表中的值可能是迭代计算中的中间值.

The gSheet API doesn't "return early", but it is possible that the values in your sheet are intermediate values mid iterative calculation.

gSheets具有最大的迭代计算限制，您可以在其中设置:

gSheets have a maximum iterative calculation limit, which you can set in:

文件>电子表格设置>计算>最大迭代次数

File > Spreadsheet Settings > Calculation > Max number of iterations

gSheet API将返回的值是最终迭代中工作表的状态.

Whatever the state of the sheet is by the final iteration are the values that will be returned by the gSheet API.

正如您所指出的那样，这可能导致结果具有中间值或一半"值.

As you point out, this can lead to the results having intermediate or "half" values.

一个潜在的解决方案是重新格式化某些电子表格计算，以确保最终值由gSheet解释程序在相同的迭代滴答中进行解析.

A potential solution is to reformat some of your spreadsheet calculations to ensure that final values are resolved by the gSheet interpreter at the same iterative tick.

例如，假设我们有以下电子表格:

For instance, say we have the following spreadsheet:

  |      A      |                    B                       | C |           D  
1 | fruit_input | =if(fruit_input = "", "", IMPORTRANGE(...))|   | =vlookup(A1,B:C,2) 

A1是我们从API输入的内容.我们希望从D1获取值.

A1 is our input from the API. We're hoping to get the value from D1.

当我们在A1中输入一个值时，B1评估为True并导入一个范围(可能是价格). D1然后执行B:C查找，从a1中搜索水果并返回col C中的值(从importrange返回).

When we input a value into A1, B1 evaluates to True and imports a range (of prices, perhaps). D1 then performs a lookup of B:C, searching for the fruit from a1 and returning the value in col C (returning from th importrange).

这看起来像是一行操作，但实际上是两个不同的迭代.如果您关闭了迭代计算或迭代计算太低(或用完了)，即使直接访问电子表格时，您会在B:C中看到值，但D2中的返回值将为空.

This may look like a single line of operations, but it is actually two different iterations. If you have iterative calculations turned off, or too low (or they run out), you'll have a blank return value from D2, even though when you visit the spreadsheet directly, you see values in B:C.

一种快速的诊断方法是，当您在电子表格中时，尝试编辑单个空白单元格，这将导致电子表格重新计算. importrange不会再次触发，但是我敢打赌，D2中现在有一个值.

A quick way to diagnose this is when you're in the spreadsheet, try editing a single blank cell, which will cause th spreadsheet to recalculate. The importrange won't trigger again, but I bet there is now a value in D2.

强制gSheets将D2的计算包括在第一次迭代中的一种方法是添加IF语句包装，如下所示:

One way to force gSheets to include the calculation of D2 into the first iteration is to add an IF statement wrapper, like so:

|               D              |
|=if(B1="","",vlookup(A1,B:C,2)|

通过包含IF包装器，您可以强制Google表格将D1中的公式包含到同一迭代中，与B1相同的调用堆栈，然后该表格将不会解析，直到B1然后是解决.

by including the IF wrapper, you force google sheets to include the formula in D1 onto the same iteration, the same call stack as B1, and then the sheet will not resolve until B1 and then D1 resolve.

这篇关于有没有一种方法可以在执行batchGet之前等待Google表格完成计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！