Hello everyone, I’m trying to export all the data of a company in a Google sheet. I’ve success to make my script work, but now the next steps is to improve the excel files to make a dropdown menu(already done) associated to each products and all deals associated with. In fact I saw in pipedrive API reference that I can call ONE product with all is deals associated. But if I want a dynamical spreadsheet (that give me all my info in live) I can’t call all my product one by one…
So my only idea is to do a first call to get all the id products and put them in a table, then put the table in a variable.
And do a new request with my variable(NewId) [/deals/{id}/products] in a loop (with my NewId.lenght in the exemple).
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=XXXXXXXXXXXXXXXXXXXXXX"
//let ui = SpreadsheetApp.getUi();
//ui.alert("toto");
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 < 10; i++) {
data = dataSet.data[i];
rows.push([data.id])
for(let idNew in rows) {
let idNew = data.id;
let urli = "https://laptop.pipedrive.com/v1/products/"+ idNew +"/deals:(name)?start=";
let response1 = UrlFetchApp.fetch(urli+start+token); //
let dataAll1 = JSON.parse(response1.getContentText());
let dataSet1 = dataAll1;
let rows1 = [], data1;
for (let i = 0; i < idNew.length; i++) {
data1 = dataSet1.data1[i];
rows1.push([data1.name])
}
}
}
Logger.log( JSON.stringify(rows,null,2) ); // Log transformed data
return rows;
}
I’ve found on the forum this pseudo-code :
DEALS = GET /deals
for DEAL in DEALS
DEAL_ID = DEAL.ID
PRODUCTS = GET /deals/DEAL_ID/product
I guess it’s not the right syntax right now, I’m sorry, it’s not my area of expertise, if there’s someone nice enough to help me rewrite this code it would be really great!
Thanks in advance, have a nice day!
Ok nobody seem’s to be interested but I will put what I managed to done. So I have successfully add all the product related to all the product but the code take 60seconds to execute (30s maximum by google script) so the result is not showing up in me sheet but I see it in the execution of app Script.
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=XXXXXXXXXXXXXXX";
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=XXXXXXXXXXXXXXXXX"
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;
}
}
I wanted to optimize it so with help I rewrote this one :
//Make the initial request to get the ids you need for the details.
var idsListRequest = "https://laptop.pipedrive.com/v1/products:(id)?start=";
var start = 0;
var limit = "&limit="+apiRequestLimit;
var token = "&api_token=XXXXXXXXXXX";
var response = UrlFetchApp.fetch(idsListRequest+start+limit+token);
var data = JSON.parse(response.getContentText()).data;
//For every id in the response, construct a url (the detail url) and push to a list of requests
var requests = [];
data.forEach(function(product){
var productDetailUrl = "https://laptop.pipedrive.com/v1/products/"+product.id+"/deals:(id,d93b458adf4bf84fefb6dbce477fe77cdf9de675)?start=";
requests.push(productDetailUrl+start+limit+token)
})
//With the list of detail request urls, make one call to UrlFetchApp.fetchAll(requests)
var allResponses = UrlFetchApp.fetchAll(requests);
// console.log(allResponses);
return allResponses;
}
But this time it’s to quick I outpass the limit of the api token if someone have solution I take it. And please it would be nice to get a response I putting a lot of effort in there, and I think this could be useful to a lot of people.