.getValue（）有时在读取ImportXML单元格时返回＃N / A [英] .getValue() return #N/A when reading ImportXML cell sometimes

问题描述

我写了一个脚本，将一个ImportXML公式写入单元格，然后几秒钟后尝试读取并用它的返回值替换单元格。

问题是我经常（但并非总是）用取出的值替换单元格时得到＃N / A。问题是我能够在短时间内看到正确的值，所以这个值被ImportXML取回并正确返回，它被重写到电子表格时会变得混乱。

示例代码：

  myformula ='= ImportXML（http：//api.something/01。 XML， /提供/价格）; 
 sheet.getRange（A1）。setFormula（myformula）; 
 Utilities.sleep（5000）; 
 sheet.getRange（A1）。setValue（sheet.getRange（A1）。getValue（））; 
  

我注意到，当URL最近被获取（并且被Google内部缓存）获得正确的值。

关于如何解决这个问题的建议？

解决方案

您的脚本无法收到电子表格已完成重新计算的通知。重新计算的时间不确定。显然，你的5秒延迟通常是足够的，但有时候重新计算需要更长的时间。

将 sleep（）替换为

  myformula ='= ImportXML（http：//api.something/01 .XML， /报价/价格）; 
 sheet.getRange（A1）。setFormula（myformula）; 
 while（sheet.getRange（A1）。getValue（）===＃N / A）{
 Utilities.sleep（5000）; 
 
 sheet.getRange（A1）。setValue（sheet.getRange（A1）。getValue（））; 
  

---

说，每次它执行，我看到在单元格中的正确值，然后我重写它后，它得到＃N / A。换句话说，如果我不重写这个值，我在单元格中看到的结果少于一秒。 - Mille 2013年1月14日在19:32

这是云计算现实的一部分。电子表格对象正在更新，并与正在处理原始缓存副本的所有查看者共享。缓存的副本与原始副本同步，但这不是即时的。当您查看工作表时，您是一名用户。您的脚本正在另一台谷歌服务器上执行云端，尽管它最终会看到与您的电子表格相同的版本，但可能需要一段时间。有两个用户进行更改（你+脚本）需要多个同步操作，所以有时候受影响的单元格将处于不稳定状态。

有一个公开问题1131 与此类似，但有些解决方法在评论中。

I've written a script that writes an ImportXML formula into a cell and then seconds later try to read and replace the cell with it's returning value.

The problem is I often (but not always) get #N/A when replacing the cell with the fetched value. The thing is that I am able to see the correct value for a short period of time, so the value gets fetched and return correctly by ImportXML, it's when rewritten to the spreadsheets it gets messed up.

Example code:

myformula = '=ImportXML("http://api.something/01.xml","/offers/price")';
sheet.getRange("A1").setFormula(myformula);
Utilities.sleep(5000);
sheet.getRange("A1").setValue(sheet.getRange("A1").getValue());

I've noticed that when the URL has been fetch recently (and is cached internally by Google) it gets the value correct.

Suggestions on how to solve this?

解决方案

There is no way for your script to receive notification that the spreadsheet has completed recalculation. The elapsed time for recalculation is non-deterministic. Obviously, your 5 second delay is often sufficient, but sometimes the recalc takes longer.

Replace sleep() with a retry loop to extend the window of time.

myformula = '=ImportXML("http://api.something/01.xml","/offers/price")';
sheet.getRange("A1").setFormula(myformula);
while (sheet.getRange("A1").getValue() === "#N/A") {
  Utilities.sleep(5000);
}
sheet.getRange("A1").setValue(sheet.getRange("A1").getValue());

---

But like I said, every time it executes I see the right value in the cell, then after I rewrite it, it gets #N/A. So in other word, if I don't rewrite the value, I see the result in less then one second in the cell. – Mille Jan 14 '13 at 19:32

This is part of the reality of cloud computing. The spreadsheet object is being updated, and shared with all viewers who are working on cached copies of the original. Cached copies are synchronized with the original, but that isn't instantaneous. When you're viewing the sheet, you're one user. Your script is executing 'in the cloud' on another google server, and while it will eventually see the same version of the spreadsheet as you, it could take a while. Having two users making changes (you+script) requires multiple synchronization operations, so at times the affected cells will be in limbo.

There's an open issue 1131 that is similar to this, with some work-arounds in the comments.

这篇关于.getValue（）有时在读取ImportXML单元格时返回＃N / A的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！