Error using worksheet.getrange.setvalues in Google Script. I have two arrays which appear to be iden-CodePudding

myVars is an array that holds variable names and their values.

Here is my code:

      var tmpArr = [];
      var tmpArrVals = "";
      
      for (i = 0; i < 4; i  ) {
        
        tmpArrVals = tmpArrVals   myVars[i][myCol]   ", ";
      }
        tmpArrVals = tmpArrVals.substr(0,tmpArrVals.length-2); //Trim last comma
        // Logger.log("tmpArrVals is "   tmpArrVals);

        
        tmpArr.push([tmpArrVals]);
      Logger.log('Contents of tmpArr are on next line.');
      Logger.log(tmpArr);
      
      var tstArr = [["Trip_Number", "Miles", "Empty_Miles", "Temp"]];
      Logger.log('Contents of tstArr are on next line');
      Logger.log(tstArr);

    wsDB.getRange(1,1,1,4).setValues(tmpArr);
    // wsDB.getRange(1,1,1,4).setValues(tstArr);

Here is the output from Logger:

3:36:28 PM  Notice  Execution started
3:36:28 PM  Info    Contents of tmpArr are on next line.
3:36:28 PM  Info    [[Trip_Number, Miles, Empty_Miles, Temp]]
3:36:28 PM  Info    Contents of tstArr are on next line
3:36:28 PM  Info    [[Trip_Number, Miles, Empty_Miles, Temp]]
3:36:29 PM  Error   
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 4.

What baffles me is that if I change the last two lines of code so that I use tstArr instead of tmpArr, it works as expected and inserts values into the first four columns of the sheet, even though the contents of the two arrays appear to be exactly the same.

CodePudding user response：

I thought that in your script, tmpArr is the array like [["text value"]]. By this, when you run the script of wsDB.getRange(1,1,1,4).setValues(tmpArr), such error occurs. I thought that this might be the reason for your issue. In order to remove this issue, how about the following modification?

From:

tmpArr.push([tmpArrVals]);

To:

tmpArr.push(tmpArrVals.split(", "));

From:

for (i = 0; i < 4; i  ) {
  
  tmpArrVals = tmpArrVals   myVars[i][myCol]   ", ";
}
  tmpArrVals = tmpArrVals.substr(0,tmpArrVals.length-2); //Trim last comma
  // Logger.log("tmpArrVals is "   tmpArrVals);

  
  tmpArr.push([tmpArrVals]);

To:

for (i = 0; i < 4; i  ) {
  tmpArr.push(tmpArrVals   myVars[i][myCol]);
}
tmpArr = [tmpArr];