AppScript: 'number of columns in the data does not match the number of columns in the range.' setValues method not reading array correctly?

user_10682921 0
Viewed 8708

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.

google-apps-script google-sheets arrays
3 Answers
  • 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);
user_115997890

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.

user_106829210

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

therefore

?.setValues(correct)

user_175944110
Related