Google表格脚本修复可预测的错误 [英] Google Sheets script to fix predictable error

问题描述

因此，我有一个Google表单跟踪里程，并通过运行瑜珈的IMPORTXML功能在这里。问题是......它超时。很多。它可能会在25％的时间内抛出＃N / A。我使用copyDown加载项将其应用于每个表单条目，因此最简单的解决方案就是使用原始公式转到单元格，将其复制并粘贴到犯错误的单元格中（这似乎也提高了我的机会首先清除单元格内容）

我已经为此表设置了一个自定义菜单，并且想要添加一个脚本来修复错误。我只是无法弄清楚开始的方向（我对脚本不太了解，更多的是从互联网上复制一些东西并将其调整为我的需求类型）。最终的目标是让它从第3行搜索一个特定的列，找出其中包含＃N / A的单元格，清除它们，从R2粘贴公式，然后将它们加载到结果中，然后仅复制/粘贴值所以函数不必重新计算。我想知道是否仍然有错误，他们可以再次做到这一点......我宁愿不让它运行一个无限循环（尽管我可能偶尔会将它粘在时间触发器上）

谢谢！

解决方案

我会通过消除对IMPORTXML功能的需求来解决这个问题。

您提到您已经在使用Google Apps脚本编写程序，因此下一步就是实施一个脚本，该脚本执行IMPORTXML功能的功能 - 计算两点之间的距离。 p>

您可以使用 onFormSubmit 触发器执行一个选择函数，并通过传递一个参数（即函数runsWhenFormSubmits（event）），从而使您的功能可以访问表单提交 object 。

您可以使用内置Maps API 。您链接的电子邮件主题有实际的距离代码：

  function getDirections（start，finish）{
 //开始&完成是可行的文本地址
 var df = Maps.newDirectionFinder（）; 
 df.setOrigin（start）.setDestination（finish）; 
 return df.getDirections（）; 
} 
  

对象布局显示为这里（以JSON格式）。

总结：

 函数runsWithForm（e）{
 var origin =123 Fake Street，Springfield，IL，
 dest =321 Real Street，Springfield，MO; 
 //当通过脚本编辑器调用时，事件对象不存在。 
 if（e）{
 var formItems = e.response.getItemResponses（）; 
 var startAddressIndex = 0; //如果起始地址是第一个答案。 
 var destinationAddressIndex = 1; //如果目的地地址是第二个答案。 
 //假设响应是一个简单的String（与String []等）。 
 origin = formItems [startAddressIndex] .getResponse（）; 
 dest = formItems [destinationAddressIndex] .getResponse（）; 
} 
 var directions = getDirections（origin，dest）; 
 if（directions.routes.length == 0）
 throw new Error（No routes between'+ origin +'and'+ dest +'。）; 
 
 //添加代码以完成现有路线的任务。 
} 
  

So I've got a Google form that's tracking mileage, and it calculates by running Yogi's IMPORTXML function here. The problem is...it times out. A lot. It throws #N/A maybe 25% of the time. I use the copyDown add-on to apply it to each form entry, so the easiest solution is just to go to the cell with the original formula, copy it, and paste it into the erring cell (it also seems to improve my chances to clear the cell contents first)

I've already set up a custom menu for this sheet, and would like to add a script to fix the errors too. I just can't quite figure out what direction to start in (I'm not very savvy with scripting...more the "copy something from the internet and tweak it to my needs" type). The ultimate goal is to have it search a specific column from Row 3 down, identify cells with #N/A in them, clear them, paste the formula from R2Cwhatever into them, let them load a result, then copy/paste the value only so the function doesn't have to keep recalculating. I figure if there are still errors, they can do it again...I'd rather not have it run an infinite loop (though I might occasionally stick it on a time trigger as well)

Thanks!

解决方案

I would approach this by eliminating the need for the IMPORTXML function.

You mention you're already writing in Google Apps Script, so the next step is to implement a script that does what the IMPORTXML function is doing - calculate the distance between 2 points.

You can use an onFormSubmit trigger to execute a function of choice, and by passing a parameter (i.e. function runsWhenFormSubmits(event)) and thus enabling your function to access the form submission object.

You'd use the built-in Maps API. The email thread you link has the actual distance code already:

function getDirections(start, finish) {
    // Start & finish are viable text addresses
    var df = Maps.newDirectionFinder();
    df.setOrigin(start).setDestination(finish);
    return df.getDirections();
}

The object layout is shown here (in JSON format).

To summarize:

function runsWithForm(e) {
  var origin = "123 Fake Street, Springfield, IL",
      dest = "321 Real Street, Springfield, MO";
  // When called via the Script Editor, the event object is not present.
  if(e) {
    var formItems = e.response.getItemResponses();
    var startAddressIndex = 0; // If the starting address is the first answer.
    var destinationAddressIndex = 1; // If the destination address is the 2nd answer.
    // Assuming the response is a simple String (vs. String[], etc).
    origin = formItems[startAddressIndex].getResponse();
    dest = formItems[destinationAddressIndex].getResponse();
  }
  var directions = getDirections(origin, dest);
  if(directions.routes.length == 0)
    throw new Error("No routes found between '" + origin + "' and '" + dest + "'.");

  // Add code to do stuff with the existing route.
}

这篇关于Google表格脚本修复可预测的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！