Pipedrive API GET request setValues of array in Google Sheet not working

Question

I have this array that I'm pulling from Pipedrive using API: [John Smith, [555-867-5309, 444-867-5309], [[email protected], [email protected]]] I want this array to populate to the Google sheet like so:

         |       A        |               B                |                    C               |
=========+========+========+========+========+========+========+========+========+===============
    1    |   John Smith   |   555-867-5309, 444-867-5309   |   [email protected], [email protected]   |

I'm using setValues but am getting an error that the number[] doesn't match the method signature for range.setValues so it's not pasting anything to the sheet.

Here's my code. It works to pull in the data. I just want to pull the three fields (name, phone, and email) of all the data it returns.

var URL = "https://....pipedrive.com";
var API_TOKEN = "...";
function test() {
  var sheeturl = 'https://docs.google.com/spreadsheets/d/...';
  var ss = SpreadsheetApp.openByUrl(sheeturl);
  var leadsSheet = ss.getSheetByName('Leads Sheet');
  var personData = [];
  
  var personurl = URL +'/v1/persons/3812?api_token='+ API_TOKEN;
  var options = {
    "method": "get",
    "contentType": "application/json",
  };
  var response = UrlFetchApp.fetch(personurl, options);
  response = JSON.parse(response.getContentText());
  var name = response.data.name;
  var phone = [response.data.phone.map(phone => phone.value).join(', ')];
  var email = [response.data.email.map(email => email.value).join(', ')];
  var personArray = [name, phone, email];
  Logger.log(personArray);
  personData.push(personArray);
  leadsSheet.getRange(leadsSheet.getLastRow()+1, 5, personData.length, personData[0].length).setValues(personData); //writes to end of sheet
}

The log from this is: [20-10-25 16:29:34:339 EDT] [John Smith, [555-867-5309, 444-867-5309], [[email protected], [email protected]]] but nothing populates to the sheet; it remains blank.

Answer 1

Issue:

personArray returns:

[John Smith, [555-867-5309, 444-867-5309], [[email protected], [email protected]]]

but your goal is to return:

[[John Smith, 555-867-5309,444-867-5309, [email protected],[email protected]]]

where 555-867-5309,444-867-5309 and [email protected],[email protected] is a single string element separated by comma.

To convert: ['555-867-5309', '444-867-5309'] to '555-867-5309,444-867-5309' you can use Array.prototype.toString().

---

Solution:

Replace:

var personArray = [name, phone, email];

with:

var personArray = [name,phone.toString(),email.toString()]

Complete snippet:

var URL = "https://....pipedrive.com";
var API_TOKEN = "...";
function test() {
  var sheeturl = 'https://docs.google.com/spreadsheets/d/...';
  var ss = SpreadsheetApp.openByUrl(sheeturl);
  var leadsSheet = ss.getSheetByName('Leads Sheet');
  var personData = [];
  
  var personurl = URL +'/v1/persons/3812?api_token='+ API_TOKEN;
  var options = {
    "method": "get",
    "contentType": "application/json",
  };
  var response = UrlFetchApp.fetch(personurl, options);
  response = JSON.parse(response.getContentText());
  var name = response.data.name;
  var phone = [response.data.phone.map(phone => phone.value).join(', ')];
  var email = [response.data.email.map(email => email.value).join(', ')];
  var personArray = [name,phone.toString(),email.toString()];
  Logger.log(leadsSheet.getLastRow()) // <- this should give the last row with content. Scroll down to your sheet to confirm.
  personData.push(personArray);
  leadsSheet.getRange(leadsSheet.getLastRow()+1, 5, personData.length, personData[0].length).setValues(personData); //writes to end of sheet
}

---

Minimal reproducible example:

function myFunction(){

const sheet = SpreadsheetApp.getActive().getSheetByName('Leads Sheet');
var personData=[];
var name = 'John Smith' 
var phone = ['555-867-5309', '444-867-5309'];
var email = ['[email protected]', '[email protected]'];
var personArray = [name,phone.toString(),email.toString()];
personData.push(personArray);
sheet.getRange(1,1,personData.length,personData[0].length).setValues(personData);
}

Result:

---

Related Article:

What does the range method getValues() return and setValues() accept?