Parallelizing requests with fetchAll in a custom function causes it to crash

Question

I'm trying to optimize this code to make it as short as possible I use it to call an API to get data in a Google Sheet. I've been told that it's the fetch that makes the script so long, and that I could try with a fetchAll but it breaks my code, I feel like putting my url in an array breaks my code (for the fetchAll). I also had suspicions about the if statement that I put in case the data is null (already made my function crash).

    // 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;
  }
}

Try with 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;
  }
}

So I saw in the documentation that I must put my URL in Tab[] to make the request but know I have the following error :

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

I suppose I'm doing something wrong but can't see it. Thanks

Answer 1

So yes I didn't put the modifications online but in fact I managed to do what I want, so I will put my code and some explanation.

First of all I didn't manage to execute the script above, or any other that I put online for two reasons :

- 1 : The first that I wrote was too long for google sheet, the execution time was above 50s (max 30s)

- 2 : The second script that I made, was too fast for the API pipedrive and when I manage to solve this issue, I've got an error saying "cannot read property of null" it was because pipedrive was returning "null" in string and null as an empty value, so this was breaking the code. When I manage to solve this the script was again too long.

So I rework the script again and put it with a start and a limit in parameters. So now I am calling my function by doing this =getPipeDriveDeals(0, 50) and =getPipeDriveDeals(51, 90) etc....

So this is the code that I wrote :

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;
}

If you want to add specific data to your sheet just change it in rows.pushand in the Url of you second call.

I advise you to not make modification of the data you want to get with you'r first request (the ID is use to make all the other request).

I hope this will help you and all the people that want to tweak Pipedrive functionality.