从脚本调用的Google文档ImportXML [英] Google docs ImportXML called from script

问题描述

我在Google文档表中使用ImportXML从sistrix api获取数据.它工作正常，但是我在一张纸上遇到了50个ImportXML命令的限制.因此，我使用了一个脚本，该脚本将ImportXML命令写到一个单元格(临时)公式中，并取回该单元格的结果值并将其复制到目标单元格中​​.因此，您可以根据需要执行很多ImportXML查询，因为它们仅显示在工作表的一个临时单元格中. 这里的问题是，ImportXML查询SOMETIMES需要很长时间或返回N/A.

I am using ImportXML in a google docs sheet to aqcuire data from the sistrix api. It works fine but I encountered the limitation of 50 ImportXML commands in one sheet. So I used a script that writes the ImportXML command to a cell (temporary) formula and takes back the resulting value of the cell and copies it to the destination cell. So you can do as much ImportXML queries as you need, as they only appear in one temporary cell in the sheet. The problem here is, that the ImportXML query SOMETIMES takes very long or returns with N/A.

是否有可能我的脚本有时不等待ImportXML查询返回，从而导致结果损坏?我目前正以这种方式进行操作:

Is it possible that my script sometimes doesnt wait for the ImportXML query to return and so the result is corrupted? I am currently doing it in this way:

function GetFormulaData(formula, sheet, row, col)
{

// write the formula (ImportXML(...)) to the specified cell
sheet.getRange(row, col).setFormula(formula);

// return the value of this cell resulting from the formula
return sheet.getRange(row, col).getValue();  
}

因此，这显然只有在完成公式(ImportXML查询)并将返回值写入单元格后才有效，因此我以后可以阅读. 有没有从脚本调用ImportXML的经验或替代方法?

So this can obviously only work if the formula (the ImportXML query) is done and has written the return value into the cell, so I can read afterwards. Does anybody have experience or alternatives with calling ImportXML from a script?

问候 密贝克(Michbeck)

Greets Michbeck

我现在已经以不同的方式解决了这个问题.与ImportXML相比，在Google文档脚本中使用UrlFetchapp()更为常见.但是您必须自己从http响应中获取xml数据.这个问题可以解决.

I have solved this now in a different way. It is more common to use UrlFetchapp() within google doc scripts than ImportXML. But you have to gain the xml data yourself from the http response. This questions can be closed.

推荐答案

我现在已经以不同的方式解决了这个问题.与ImportXML相比，在Google文档脚本中使用UrlFetchapp()更为常见.但是您必须自己从http响应中获取xml数据.

I have solved this now in a different way. It is more common to use UrlFetchapp() within google doc scripts than ImportXML. But you have to gain the xml data yourself from the http response.

我现在是这样的:

var response = UrlFetchApp.fetch(apiString).getContentText();
var xmlContent = Xml.parse(response, true);
var answer = xmlContent.response.answer; 

// get what you need from the XML answer
if (answer != null)
{
    var element = answer.getElement('foo');
    if (element != null)
    {
        var attrib = element.getAttribute('bar');    
        if (attrib != null)
            value = attrib.getValue();  // the value you want
    }
}

这篇关于从脚本调用的Google文档ImportXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！