CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Scott Fitzpatrick
Scott Fitzpatrick

Reputation: 41

Exception: The number of columns in the data does not match the number of columns in the range

Hello I am struggling with the syntax and functions of what I am trying to do. I loop a list of values and run a database operation that takes longer than 5 minutes so I track which values on the list are inserted in column B. But the first time the script runs there is no column B and that seems to confuse the last statement when writing the array back.

So I think the problem is creating a 1 dimensional array then adding a column and then trying to write it back as a two column array.

I took out the database code to show just the problem code.

    const LISTRANGE=LISTSHEET.getDataRange(); //get all the values just column A is populated
    var LISTARRAY=LISTRANGE.getValues(); // push them into an array
    for (var i = 0; i < NEXTRUN ; i++) { // loop the array
      LISTARRAY[NEXTRUN][1] = "INSERTED"
    }
    LISTSHEET.getRange(1,1,LISTARRAY.length,2).setValues(LISTARRAY);

This code ends with the result

Exception: 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 2. (line 76, file "macros")

I thought maybe I need to select only 1 column since the array started 1 dimensional but then it gives this error

    LISTSHEET.getRange(1,1,LISTARRAY.length,1).setValues(LISTARRAY);

Exception: The number of columns in the data does not match the number of columns in the range. The data has 2 but the range has 1. (line 76, file "macros")

Can you help me with a strategy to modify this code to avoid the error? I don't mind a different approach I am just trying to keep the processing in the cache so I am not writing to the sheet on each loop iteration.

Upvotes: 0

Views: 310

Answers (1)

Cooper
Cooper

Reputation: 64040

try this:

const LISTRANGE=LISTSHEET.getRange(1,1,LISTSHEET.getLastRow(),2);
var LISTARRAY=LISTRANGE.getValues(); 
for (var i=0;i<LISTARRAY.length;i++) {
  LISTARRAY[i][1] = "INSERTED"
}
LISTSHEET.getRange(1,1,LISTARRAY.length,LISTARRAY[0].length).setValues(LISTARRAY);

Upvotes: 2

Related Questions