在自定义函数中将请求与fetchAll并行会导致它崩溃

Question

我正在尝试优化此代码，使其尽可能短。我使用它来调用API以获取Google工作表中的数据。有人告诉我，是fetch让脚本变得很长，我可以尝试使用fetchAll，但它会破坏我的代码，我觉得把我的url放在数组中会破坏我(对于fetchAll(的代码。我还怀疑我放入的if语句，以防数据是null(已经使我的函数崩溃(。

// Standard functions to call the spreadsheet sheet and activesheet
function GetPipedriveDeals2() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();
let sheet = ss.getActiveSheet();
//the way the url is build next step is to iterate between the end because api only allows a fixed number of calls (100) this way i can slowly fill the sheet.
let url    = "https://laptop.pipedrive.com/v1/products:(id)?start=";
let limit  = "&limit=500";
//let filter = "&filter_id=64";
let pipeline = 1; // put a pipeline id specific to your PipeDrive setup 
let start  = 1;
//let end  = start+50;
let token  = "&api_token=XXXXXXXXXXXXXXXXXXXXXXXX"

let response = UrlFetchApp.fetch(url+start+limit+token); //
let dataAll = JSON.parse(response.getContentText()); 
let dataSet = dataAll;
//let prices = prices;
//create array where the data should be put
let rows = [], data;
for (let i = 0; i < dataSet.data.length; i++) {
data = dataSet.data[i];
rows.push([data.id,
GetPipedriveDeals4(data.id)
]);
}
Logger.log( 'function2' ,JSON.stringify(rows,null,8) );   // Log transformed data
return rows;
}
// Standard functions to call the spreadsheet sheet and activesheet
function GetPipedriveDeals4(idNew) {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();
let sheet = ss.getActiveSheet();
//the way the url is build next step is to iterate between the end because api only allows a fixed number of calls (100) this way i can slowly fill the sheet.
let url    = "https://laptop.pipedrive.com/v1/products/"+idNew+"/deals:(id,d93b458adf4bf84fefb6dbce477fe77cdf9de675)?start=";
let limit  = "&limit=500";
//let filter = "&filter_id=64";
let pipeline = 1; // put a pipeline id specific to your PipeDrive setup 
let start  = 1;
//let end  = start+50;
let token  = "&api_token=XXXXXXXXXXXXXXXXXXXXXX"

let response = UrlFetchApp.fetch(url+start+limit+token); //
let dataAll = JSON.parse(response.getContentText()); 
let dataSet = dataAll;
//Logger.log(dataSet)
//let prices = prices;
//create array where the data should be put
let rows = [], data;
if(dataSet.data === null )return
else {
for (let i = 0; i < dataSet.data.length; i++) {
data = dataSet.data[i];
let idNew = data.id; 
rows.push([data.id, data['d93b458adf4bf84fefb6dbce477fe77cdf9de675']]);
}

Logger.log( 'function4', JSON.stringify(rows,null,2) );   // Log transformed data
return rows;
}
}

试用fetchAll:

// Standard functions to call the spreadsheet sheet and activesheet
function GetPipedriveDeals2() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();
let sheet = ss.getActiveSheet();
//the way the url is build next step is to iterate between the end because api only allows a fixed number of calls (100) this way i can slowly fill the sheet.
let limit  = "&limit=500";
//let filter = "&filter_id=64";
let pipeline = 1; // put a pipeline id specific to your PipeDrive setup 
let start  = 1;
//let end  = start+50;
let token  = "&api_token=XXXXXXXXXXXXXXXXXXXXXX"
let url    = "https://laptop.pipedrive.com/v1/products:(id)?start="+start+limit+token;
let request = [url];
let response = UrlFetchApp.fetchAll(request); //
let dataAll = response.map(function(e) {return e.getContentText()});
let dataSet = dataAll;
//let prices = prices;
//create array where the data should be put
let rows = [], data;
for (let i = 0; i < dataSet.data.length; i++) {
data = dataSet.data[i];
rows.push([data.id,
GetPipedriveDeals4(data.id)
]);
}
Logger.log( 'function2' ,JSON.stringify(rows,null,8) );   // Log transformed data
return rows;
}
function GetPipedriveDeals4(idNew) {
let start  = 1;
let limit  = "&limit=500";
let token  = "&api_token=XXXXXXXXXXXXXXXXXXXX"
let urli    = "https://laptop.pipedrive.com/v1/products/"+idNew+"/deals:(id,d93b458adf4bf84fefb6dbce477fe77cdf9de675)?start="+start+limit+token;
let request1 = [urli]
let response1 = UrlFetchApp.fetchAll(request1); //
var dataAll1 = response1.map(function(e) {return e.getContentText()});
let dataSet1 = dataAll1;
//the way the url is build next step is to iterate between the end because api only allows a fixed number of calls (100) this way i can slowly fill the sheet.
let urli    = "https://laptop.pipedrive.com/v1/products/"+idNew+"/deals:(id,d93b458adf4bf84fefb6dbce477fe77cdf9de675)?start="+start+limit+token;
let request1 = [urli]
let response1 = UrlFetchApp.fetchAll(request1); //
var dataAll1 = response1.map(function(e) {return e.getContentText()});
let dataSet1 = dataAll1;
//Logger.log(dataSet1)
//let prices = prices;
//create array where the data should be put
let rows1 = [], data1;
if(dataSet1.data1 === null )return
else {
for (let i = 0; i < dataSet1.data1.length; i++) {
data1 = dataSet1.data1[i];
let idNew = data1.id; 
rows1.push([data1.id, data1['d93b458adf4bf84fefb6dbce477fe77cdf9de675']]);
}

Logger.log( 'function4', JSON.stringify(rows1,null,2) );   // Log transformed data
return rows1;
}
}

所以我在文档中看到，我必须将我的URL放在Tab[]中才能提出请求，但我知道我有以下错误：

8 juil. 2020 à 16:06:18 Erreur  TypeError: Cannot read property 'length' of undefined
at GetPipedriveDeals2(Copie de importNamesTypes:22:36)

我想我做错了什么，但看不见。谢谢

Answer 1

所以是的，我没有把修改放在网上，但事实上我成功地做了我想做的，所以我会放上我的代码和一些解释。

首先，由于两个原因，我没能执行上面的脚本，或者我放在网上的任何其他脚本：

-1：我写的第一个对谷歌表单来说太长了，执行时间超过了50秒(最多30秒(

-2：我制作的第二个脚本对于API管道驱动器来说太快了，当我设法解决这个问题时，我出现了一个错误，说"；无法读取null的属性"；这是因为pipedrive正在返回"；空"；在字符串中，null为空值，所以这破坏了代码。当我设法解决这个问题时，脚本又太长了。

所以我再次修改了这个脚本，并在参数中添加了一个开始和限制。所以现在我通过做=getPipeDriveDeals(0, 50)和=getPipeDriveDeals(51, 90)等来调用我的函数…

这就是我写的代码：

function getPipedriveDeals(start , limit) {
var allResponsesDeals = [];

options = {muteHttpExceptions: true}; 
var idsListRequest = "https://xxxx.pipedrive.com/v1/products:(id)?start=";
var token = "&api_token=hiddenforobviousreasons";
var response = UrlFetchApp.fetch(idsListRequest + start +"&limit="+limit + token, options);
let dataAll = JSON.parse(response.getContentText()).data;

var requests = [];
let rows = [], data;
//Logger.log("data="+ JSON.stringify(dataAll, null, 2));


if(dataAll === null) {
//Logger.log(" dataAll issss nnnnuulll" );
}else{ 
dataAll.forEach(function(product) {
var productDetailUrl = "https://xxxx.pipedrive.com/v1/products/" + product.id + "/deals:(title,7d321c7454a4b44a09f32bdd7702a2b17fd7654e)?start=0";
requests = productDetailUrl + token
var responses = UrlFetchApp.fetch(requests);
var dataArray = JSON.parse(responses.getContentText());


if(dataArray.data === null) {
//Logger.log(" newData issss nnnnuulll" );
}else 
{
for (let i = 0; i < dataArray.data.length; i++) {
data = dataArray.data[i];
rows.push([data.title, data['7d321c7454a4b44a09f32bdd7702a2b17fd7654e']])
}
}
});
}

Logger.log("allResponsesDeals ="+ JSON.stringify(rows, null, 2));
return rows;

}
function getAllDeals(){
var allResponses = [];
var deals = getPipedriveDeals();
Logger.log("deals="+ JSON.stringify(deals, null, 2));

deals.forEach((response)=>{allResponses.push(response)});

allResponses.push(deals)

Logger.log("allResponses="+ allResponses);
return allResponses;
}

如果您想将特定数据添加到工作表中，只需在rows.push和第二次调用的Url中进行更改即可。

我建议你不要修改你想在第一次请求时获得的数据(ID用于进行所有其他请求(。

我希望这将帮助你和所有想要调整Pipedrive功能的人。