脚本＆amp;在试图在Google脚本中打印大型数组时的表格计时

Question

背景

I具有一个函数，可以在Google脚本中使用urlfetchapp进行REST API调用。

但是响应仅一次返回2000年的记录。如果有更多记录，则在响应中，有一个称为 nextrecordsurl 的键，其中包含获取下一批记录所需的端点和参数。

我使用做...而循环遍历遍历，将记录推入预先指定的数组，进行下一个API调用。当它达到最后一批记录时，它存在于do-e-lile循环时，然后打印（不确定在这里这是正确的术语），整个是Google表。

代码

它看起来像这样的

function getCampaignAssociations() {
  clearPage('CampaignAssociations');
  var query = '?q=select+CampaignMember.FirstName,CampaignMember.LastName,CampaignMember.LeadId,CampaignMember.ContactId,CampaignMember.Name,CampaignMember.CampaignId,CampaignMember.SystemModstamp,CampaignMember.Email+from+CampaignMember+ORDER+BY+Email ASC,SystemModstamp+ASC';

  try {
    var arrCampAssociation = getInfoByQuery(query);

    if (arrCampAssociation.records.length < 1) {
      throw 'there are no records in this query';
    }

    var campaignAssoc = [];
    do {
      Logger.log(arrCampAssociation.nextRecordsUrl);

      for (var i in arrCampAssociation.records) {
        let data = arrCampAssociation.records[i];
        let createDate = Utilities.formatDate(new Date(data.SystemModstamp), "GMT", "dd-MM-YYYY");
        let a1 = "$A" + (parseInt(i) + 2);
        let nameFormula = '=IFERROR(INDEX(Campaigns,MATCH(' + a1 + ',Campaigns!$A$2:A,0),2),"")';
        let typeFormula = '=IFERROR(INDEX(Campaigns,MATCH(' + a1 + ',Campaigns!$A$2:A,0),3),"")';

        campaignAssoc.push([data.CampaignId, nameFormula, typeFormula, data.Email, data.FirstName, data.LastName, data.LeadId, data.ContactId, createDate]);
      }

      var arrCampAssociation = getQueryWithFullEndPoint(arrCampAssociation.nextRecordsUrl);
      
    } while (arrCampAssociation.nextRecordsUrl != null && arrCampAssociation.nextRecordsUrl != undefined);

      let endRow = campAssocSheet.getLastRow(), 
       endColumn = campAssocSheet.getLastColumn(), 
       nameRange = campAssocSheet.getRange(2, 1, endRow, endColumn),
       destRange = campAssocSheet.getRange(2, 1, campaignAssoc.length, campaignAssoc[0].length);
      destRange.setValues(campaignAssoc);
      sheet.setNamedRange('CampaignAssociation', nameRange);


    } catch (e) {
      Logger.log(e);
      Logger.log(arrCampAssociation);
      Logger.log(campaignAssoc);
      Logger.log(i);
    }
  }

：发行

一切都很好，直到将数组 ApaindAssoc 打印到Google Sheet上为止。

请参阅下面日志的屏幕截图。它包含下一个两者的端点。请注意，较早的日志和最后一个端点与日志的时间戳之间的时间戳。

在我看来，问题是在数据打印时，它存在问题。如果是这样，我是否超载了数组？总共有36400多个记录。

第二次尝试，

我尝试在每个循环中重置阵列，然后将阵列打印到Google Sheet。这只是每次尝试的2000个记录，我肯定会在1次做更多的行，但这无济于事。

这是该尝试的代码。

function getCampaignAssociations() {
  clearPage('CampaignAssociations');
  var query = '?q=select+CampaignMember.FirstName,CampaignMember.LastName,CampaignMember.LeadId,CampaignMember.ContactId,CampaignMember.Name,CampaignMember.CampaignId,CampaignMember.SystemModstamp,CampaignMember.Email+from+CampaignMember+ORDER+BY+Email ASC,SystemModstamp+ASC';

  try {
    var arrCampAssociation = getInfoByQuery(query);

    if (arrCampAssociation.records.length < 1) {
      throw 'there are no records in this query';
    }

    
    do {
      Logger.log(arrCampAssociation.nextRecordsUrl);
      var campaignAssoc = [];
      for (var i in arrCampAssociation.records) {
        let data = arrCampAssociation.records[i];
        let createDate = Utilities.formatDate(new Date(data.SystemModstamp), "GMT", "dd-MM-YYYY");
        let a1 = "$A" + (parseInt(i) + 2);
        let nameFormula = '=IFERROR(INDEX(Campaigns,MATCH(' + a1 + ',Campaigns!$A$2:A,0),2),"")';
        let typeFormula = '=IFERROR(INDEX(Campaigns,MATCH(' + a1 + ',Campaigns!$A$2:A,0),3),"")';

        campaignAssoc.push([data.CampaignId, nameFormula, typeFormula, data.Email, data.FirstName, data.LastName, data.LeadId, data.ContactId, createDate]);
        
      }
      let lastRow = campAssocSheet.getLastRow()+1;
      campAssocSheet.getRange(lastRow,1,campaignAssoc.length,campaignAssoc[0].length).setValues(campaignAssoc);
      var arrCampAssociation = getQueryWithFullEndPoint(arrCampAssociation.nextRecordsUrl);
      
    } while (arrCampAssociation.nextRecordsUrl != null && arrCampAssociation.nextRecordsUrl != undefined);

      let endRow = campAssocSheet.getLastRow(), 
       endColumn = campAssocSheet.getLastColumn(), 
       nameRange = campAssocSheet.getRange(2, 1, endRow, endColumn);

      sheet.setNamedRange('CampaignAssociation', nameRange);


    } catch (e) {
      Logger.log(e);
      Logger.log(arrCampAssociation);
      Logger.log(campaignAssoc);
      Logger.log(i);
    }
  }

因此，在这里，每个循环花费了更长的时间。而不是每个环之间的1-2秒钟，而是在第4个循环之后的每个循环中花费了45秒至一分钟。请参阅下面的日志：

我该如何解决？