投稿
  1. 码文网首页
  2. 码文文章

如何处理GoogleApps脚本6分钟执行时间限制 同时从API端点获取数据到Google工作表

作者：甜甜的维妮 • • 阅读 11

我必须从 API 获取数据到 google sheets 这应该绘制近 1600 个条目。但是,执行停止在 6 分钟绘制只有大约 1000 个条目。我在 Apps Script 中的初始代码是:

我必须从 API 获取数据到 google sheets 这应该绘制近 1600 个条目。但是,执行停止在 6 分钟绘制只有大约 1000 个条目。我在 Apps Script 中的初始代码是:

function myFunction() {
  var spreadsheet=SpreadsheetApp.getActive();
  var sheet=spreadsheet.getActiveSheet();
  var nextPage=1;
  sheet.clear();
  var headerRow=["ID","NAME","SOURCE","STATUS","PRICING_MIN_PRICE","PRICING_MAX_PRICE","LOACTION_COUNTRY","LOACTION_LOCALITY","IMAGES_COUNT","VIDEOS_COUNT","FEATURES_COUNT"];
  sheet.appendRow(headerRow);
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setFontWeight('bold');
  
  
  var curr;
  while(nextPage){
    curr=nextPage;
    var apiURL=`https://base.amberstudent.com/api/v0/inventories?p=${curr}&limit=10&sort_key=relevance&sort_order=desc&statuses=active`;
    var response=UrlFetchApp.fetch(apiURL);
    var json=response.getContentText();
    var dataPoints=JSON.p(json);
    var resArray=dataPoints.data.result;
    for(var i=0;i<resArray.length;i++){
      var id=resArray[i].id!=null?resArray[i].id:"";
      var name=resArray[i].name!=null?resArray[i].name:"";
      var source=resArray[i].source!=null?resArray[i].source:"";
      var status=resArray[i].status!=null?resArray[i].status:"";
      var pricing_min_price=resArray[i].pricing?resArray[i].pricing.min_price:"";
      var pricing_max_price=resArray[i].pricing?resArray[i].pricing.max_price:"";
      var location_country=(resArray[i].location&&resArray[i].location.country)?resArray[i].location.country.long_name:"";
      var location_locality=(resArray[i].location&&resArray[i].location.locality)?resArray[i].location.locality.long_name:"";
      var images_count=resArray[i].images.length;
      var vid_count=resArray[i].videos.length;
      var feature_count=resArray[i].features.length;
      var row=[id,name,source,status,pricing_min_price,pricing_max_price,location_country,location_locality,images_count,vid_count,feature_count];
      sheet.appendRow(row);
    }
    nextPage=dataPoints.data.meta.next;  //for every page the nextPage stores the value of the next page, and for the last page (159 approx),     nextPage=null
  }
  
}

在互联网上进行一些搜索后，我找到了一些方法来绕过执行时间，我修改了我的代码，如下所示：

var spreadsheet=SpreadsheetApp.getActive();
var sheet=spreadsheet.getActiveSheet();
var nextPage=1;   //set nextPage as a global variable so that it can be accessed by all functions
function isTimeUp(today) {
  var now = new Date();
  return now.getTime() - today.getTime() > 300000;  //setting up a limit of 5 minutes
}
function myFunction() {
  sheet.clear();
  var today=new Date();
  var headerRow=["ID","NAME","SOURCE","STATUS","PRICING_MIN_PRICE","PRICING_MAX_PRICE","LOACTION_COUNTRY","LOACTION_LOCALITY","IMAGES_COUNT","VIDEOS_COUNT","FEATURES_COUNT"];
  sheet.appendRow(headerRow);
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setFontWeight('bold');
  
  
  var curr;
  while(nextPage){
    if (isTimeUp(today)) {
        // schedule a trigger for a different function
        ScriptApp.newTrigger("repeatFunction")
            .timeBased()
            .everyMinutes(5)
            .create();
        break;
    }
    curr=nextPage;
    var apiURL=`https://base.amberstudent.com/api/v0/inventories?p=${curr}&limit=10&sort_key=relevance&sort_order=desc&statuses=active`;
    var response=UrlFetchApp.fetch(apiURL);
    var json=response.getContentText();
    var dataPoints=JSON.p(json);
    var resArray=dataPoints.data.result;
    for(var i=0;i<resArray.length;i++){
      var id=resArray[i].id!=null?resArray[i].id:"";
      var name=resArray[i].name!=null?resArray[i].name:"";
      var source=resArray[i].source!=null?resArray[i].source:"";
      var status=resArray[i].status!=null?resArray[i].status:"";
      var pricing_min_price=resArray[i].pricing?resArray[i].pricing.min_price:"";
      var pricing_max_price=resArray[i].pricing?resArray[i].pricing.max_price:"";
      var location_country=(resArray[i].location&&resArray[i].location.country)?resArray[i].location.country.long_name:"";
      var location_locality=(resArray[i].location&&resArray[i].location.locality)?resArray[i].location.locality.long_name:"";
      var images_count=resArray[i].images.length;
      var vid_count=resArray[i].videos.length;
      var feature_count=resArray[i].features.length;
      var row=[id,name,source,status,pricing_min_price,pricing_max_price,location_country,location_locality,images_count,vid_count,feature_count];
      sheet.appendRow(row);
    }
    nextPage=dataPoints.data.meta.next;  //for every page the nextPage stores the value of the next page, and for the last page (159 approx),     nextPage=null
  }
  
}
function repeatFunction(){
  while(nextPage){
    var curr=nextPage;
    var apiURL=`https://base.amberstudent.com/api/v0/inventories?p=${curr}&limit=10&sort_key=relevance&sort_order=desc&statuses=active`;
    var response=UrlFetchApp.fetch(apiURL);
    var json=response.getContentText();
    var dataPoints=JSON.p(json);
    var resArray=dataPoints.data.result;
    for(var i=0;i<resArray.length;i++){
      var id=resArray[i].id!=null?resArray[i].id:"";
      var name=resArray[i].name!=null?resArray[i].name:"";
      var source=resArray[i].source!=null?resArray[i].source:"";
      var status=resArray[i].status!=null?resArray[i].status:"";
      var pricing_min_price=resArray[i].pricing?resArray[i].pricing.min_price:"";
      var pricing_max_price=resArray[i].pricing?resArray[i].pricing.max_price:"";
      var location_country=(resArray[i].location&&resArray[i].location.country)?resArray[i].location.country.long_name:"";
      var location_locality=(resArray[i].location&&resArray[i].location.locality)?resArray[i].location.locality.long_name:"";
      var images_count=resArray[i].images.length;
      var vid_count=resArray[i].videos.length;
      var feature_count=resArray[i].features.length;
      var row=[id,name,source,status,pricing_min_price,pricing_max_price,location_country,location_locality,images_count,vid_count,feature_count];
      sheet.appendRow(row);
    }
    nextPage=dataPoints.data.meta.next;  //for every page the nextPage stores the value of the next page, and for the last page (159 approx),     nextPage=null
    if (nextPage==null) {
      var triggers = ScriptApp.getProjectTriggers();
      for (var i = 0; i < triggers.length; i++) {
          // delete all triggers
          ScriptApp.deleteTrigger(triggers[i]);
      }
      break;
    }
  }
}

我试图将 nextPage 设置为全局变量,并设置了一个触发器,该触发器每 5 分钟调用一次 repeatFunction 方法。然而,这创建了一个类似无限循环的东西。数据将 gettind 添加到电子表格中。我无法弄清楚如何克服这个问题,因为我对 Google App Scripts 的概念及其用法不熟悉。请帮助我解决这个问题。如果有必要,请询问更多细节。谢谢!

0

我相信你的目标如下。

您的myFunction()有效。但是，您希望降低脚本的过程成本。

修改点：

在您的脚本中，appendRow在循环中使用，我认为这可能是您的问题的原因之一。

这也可以在the benchmark中看到。

虽然我不确定您要使用的 API 的详细信息,但在您的端点查询参数中,使用了limit=10。而且,从I have to fetch data from an API to google sheets which is supposed to plot nearly 1600 entries.,如果limit=10是一个 API 调用的值的数量,则需要完成 160 个请求。我认为这将是您的问题的另一个原因。在这种情况下,我想建议修改的值

在此修改中，使用了limit=2000，在我的测试中，没有发生错误，但是如果发生错误，请修改此值并再次测试。

当以上几点反映到你的脚本，它变成如下。

修改的脚本：

在此修改中，请按如下方式修改您的myFunction()。

function myFunction2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var nextPage = 1;
  sheet.clear();
  var headerRow = ["ID", "NAME", "SOURCE", "STATUS", "PRICING_MIN_PRICE", "PRICING_MAX_PRICE", "LOACTION_COUNTRY", "LOACTION_LOCALITY", "IMAGES_COUNT", "VIDEOS_COUNT", "FEATURES_COUNT"];
  sheet.appendRow(headerRow);
  sheet.getRange(spreadsheet.getCurrentCell().getRow(),1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setFontWeight('bold');
  var ar = [];
  var curr;
  while (nextPage) {
    curr = nextPage;
    var apiURL = `https://base.amberstudent.com/api/v0/inventories?p=${curr}&limit=2000&sort_key=relevance&sort_order=desc&statuses=active`;
    var response = UrlFetchApp.fetch(apiURL);
    var json = response.getContentText();
    var dataPoints = JSON.p(json);
    var resArray = dataPoints.data.result;
    for (var i = 0; i < resArray.length; i++) {
      var id = resArray[i].id != null ? resArray[i].id : "";
      var name = resArray[i].name != null ? resArray[i].name : "";
      var source = resArray[i].source != null ? resArray[i].source : "";
      var status = resArray[i].status != null ? resArray[i].status : "";
      var pricing_min_price = resArray[i].pricing ? resArray[i].pricing.min_price : "";
      var pricing_max_price = resArray[i].pricing ? resArray[i].pricing.max_price : "";
      var location_country = (resArray[i].location && resArray[i].location.country) ? resArray[i].location.country.long_name : "";
      var location_locality = (resArray[i].location && resArray[i].location.locality) ? resArray[i].location.locality.long_name : "";
      var images_count = resArray[i].images.length;
      var vid_count = resArray[i].videos.length;
      var feature_count = resArray[i].features.length;
      var row = [id, name, source, status, pricing_min_price, pricing_max_price, location_country, location_locality, images_count, vid_count, feature_count];
      ar.push(row);
    }
    nextPage = dataPoints.data.meta.next;  //for every page the nextPage stores the value of the next page, and for the last page (159 approx),     nextPage=null
  }
  sheet.getRange(sheet.getLastRow() + 1, 1, ar.length, ar[0].length).setValues(ar);
}

注：

在此修改中，使用setValues代替appendRow，并使用limit=2000。在这种情况下，通过一次 API 调用检索 1,585 个值。在我的环境中，上述修改脚本的处理时间约为 20 秒。

参考文献：

Benchmark:Reading and Writing Spreadsheet using Google Apps Script setValues(values)

本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处

赞 (722)
python中是否有大于但小于函数
上一篇
当数据框中存在NaN时使用astype时出错
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表（57条）