5

I'm trying to automate the collection of phone numbers from an API into a Google Sheet with app script. I can get the data and place it in an array with the following code:

 const options = { method: 'GET', headers: { Authorization: 'Bearer XXXXXXXXXXXXXXX', Accept: 'Application/JSON', } }; var serviceUrl = "dummyurl.com/?params"; var data=UrlFetchApp.fetch(serviceUrl, options); if(data.getResponseCode() == 200) { var response = JSON.parse(data.getContentText()); if (response !== null){ var keys = Object.keys(response.call).length; var phoneArray = []; for(i = 0; i < keys; i++) { phoneArray.push(response.call[i].caller.caller_id); }

This works as expected - it grabs yesterday's caller ID values from a particular marketing campaign from my API. Next, I want to import this data into a column in my spreadsheet. To do this, I use the setValues method like so:

 Logger.log(phoneArray); var arrayWrapper = []; arrayWrapper.push(phoneArray); Logger.log(arrayWrapper); for(i = 0; i < keys; i++) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var cell = sheet.getRange("A8"); cell.setValues(arrayWrapper); } } } }

I am aware that I need my array length to equal the length of the selected range of cells in my sheet. However, I get conflicting errors depending on the length I set for my getRange method. If I set it to a single cell, as you see above, the error I get is:

The number of columns in the data does not match the number of columns in the range. The data has 8 but the range has 1.

However, if I set the length of my range to 8 (or any value except 1), I get the error:

The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 8.

As you see, the error swaps values. Now I have the appropriate number of columns in the range, but my script only finds 1 cell of data. When I check the log, I see that my 2D array looks normal in both cases - 8 phone numbers in an array wrapped in another array.

What is causing this error? I cannot find reference to similar errors on SO or elsewhere.

Also, please note that I'm aware this code is a little wonky (weird variables and two for loops where one would do). I've been troubleshooting this for a couple hours and was originally using setValue instead of setValues. While trying to debug it, things got split up and moved around a lot.

Improve this question

3 Answers 3

Reset to default
3
  • The dimension of your range is one row and several columns
  • If you push an array into another array, the dimension will be [[...],[...],[...]] - i.e. you have one column and multiple rows
  • What you want instead is one row and multiple columns: [[...,...,...]]
  • To achieve this you need to create a two-dimensional array and push all entries into the first row of your array: phoneArray[0]=[]; phoneArray[0].push(...);

Sample:

 var phoneArray = []; phoneArray[0]=[]; for(i = 0; i < keys; i++) { var phoneNumber = response.call[i].caller.caller_id; phoneNumber = phoneNumber.replace(/-/g,''); phoneArray[0].push(phoneNumber); } var range = sheet.getRange(1,8,1, keys); range.setValues(phoneArray);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

So I figured out how to make this work, though I can't speak to why the error is occurring, or rather why one receives reversed error messages depending on the setRange value.

Rather than pushing the whole list of values from the API to phoneArray, I structured my first for loop to reset the value of phoneArray each loop and push a single value array to my arrayWrapper, like so:

for(i = 0; i < keys; i++) { var phoneArray = []; var phoneNumber = response.call[i].caller.caller_id; phoneNumber = phoneNumber.replace(/-/g,''); phoneArray.push(phoneNumber); arrayWrapper.push(phoneArray); }

Note that I also edited the formatting of the phone numbers to suit my needs, so I pulled each value into a variable to make replacing a character simple. What this new for loop results in is a 2D array like so:

[[1235556789],[0987776543],[0009872345]]

Rather than what I had before, which was like this:

[[1235556789,0987776543,0009872345]]

It would appear that this is how the setValues method wants its data structured, although the documentation suggests otherwise.

Regardless, if anyone were to run into similar issues, this is the gist of what must be done to fix it, or at least the method I found worked. I'm sure there are far more performant and elegant solutions than mine, but I will be dealing with dozens of rows of data, not thousands or millions. Performance isn't a big concern for me.

Improve this answer

Comments

0

var correct = [[data],[data]] - is the data structure that is required for setValues()

therefore

?.setValues(correct)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.