From Array to setValues gives: "Cannot convert to ."

Question

BACKGROUND. I want to change a timesheet from a week format (every row shows 7 days, no date is available only week in the from yyww (e.g. 1225). In another sheet one column lists week and another lists the dates.

METHOD. I take these two sheets into two arrays, package a third array which values I set into a third sheet.

PROBLEM. This row gives the error message: "Cannot convert to ."

  sheet_IndataTabell.getRange(1,1,IndataTable.length+1,7).setValues(IndataTable);

Source. You can see the document here and the full function below:

function UpdateTable() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet_Indata = ss.getSheetByName("Indata");
var sheet_IndataTabell = ss.getSheetByName("Indata_Tabell");
var sheet_Calendar = ss.getSheetByName("Kalender");

//Get the table into arrays
var Indata = sheet_Indata.getDataRange();
var CalendarTable = sheet_Calendar.getDataRange();

//Gets the values in the Indata to an Array
var NumberRows = Indata.getLastRow();
var NumberCols = Indata.getLastColumn();
//Browser.msgBox(NumberRows + " " + NumberCols);
var IndataArray = new Array(NumberRows,NumberCols);
var IndataArray = Indata.getValues();

//Create an Array to store the result
var IndataTable = new Array(((NumberCols-1)*(NumberRows-1)),7);
//Browser.msgBox("First the IndataTable is defined as: (NumberCols-1)*(NumberRows-1) (" + (NumberCols-1)*(NumberRows-1) + ").");

//Gets the values in the Calendar to an Array
var NumberRows_Cal = CalendarTable.getLastRow();
var NumberCols_Cal = CalendarTable.getLastColumn();
//Browser.msgBox(NumberRows + " " + NumberCols);
var CalendarArray = new Array(NumberRows_Cal,NumberCols_Cal);
var CalendarArray = CalendarTable.getValues();

for (i=1; i<(IndataArray.length); i++)
{

  for (j=3; j<10; j++) {

  IndataTable[i*7+j-3-7] = {};

  //adds the users
  IndataTable[i*7+j-3-7][0] = IndataArray[i][1];

  //adds the week numbers
  var Vecka = IndataArray[i][2];
  IndataTable[i*7+j-3-7][1] = Vecka;

  //adds the hours
  IndataTable[i*7+j-3-7][2] = IndataArray[i][j];

  //adds the projects
  IndataTable[i*7+j-3-7][3] = IndataArray[i][10];

  //adds the day
  var Dag = (j-2);
  IndataTable[i*7+j-3-7][4] = Dag;

  //Gets the date from the Calendar
  IndataTable[i*7+j-3-7][5] = "=VLOOKUP(G" + (i*7+j-9) + ";Kalender!$B$1:C;2)+E" + (i*7+j-9) + "-1";

  IndataTable[i*7+j-3-7][6] = Vecka+"_"+Dag;

  //Browser.msgBox("[" + (i*7+j-3-7) + "][n]: " + IndataTable[i*7+j-3-7][0] + ", " + IndataTable[i*7+j-3-7][1] + ", " + IndataTable[i*7+j-3-7][2] + ", " + IndataTable[i*7+j-3-7][3] + ", " + IndataTable[i*7+j-3-7][4] + ", " + IndataTable[i*7+j-3-7][5] + ", " + IndataTable[i*7+j-3-7][6]);

  }

}


sheet_IndataTabell.clear();
//Browser.msgBox("IndataTable.length: " + IndataTable.length);
//Browser.msgBox("IndataTable[0].length: " + IndataTable[0].length);
//Browser.msgBox("Last row on Range: " + sheet_IndataTabell.getRange(1, 1, IndataTable.length,7).getLastRow());
//Browser.msgBox(IndataTable);

sheet_IndataTabell.getRange("A1:G28").setValues(IndataTable);

//sheet_Indata.sort(3,true); 

//Browser.msgBox("Uppdatering klar!");
sheet_IndataTabell.getRange("J1").setValue(Date());

}

Answer 1

Althought this is an old thread my answer could helps others looking here to solve this problem,

Basically this function loops through an array and checks if there is an empty slot, than it takes value from the previous slot and adds text to it.

function addText(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //Getting active spreadsheet
  var s = ss.getActiveSheet();  //Getting active sheet

  var sheetName = s.getSheetName(); //Getting active sheet name
  var lastRow = s.getLastRow(); //Getting sheets last row number

  if (sheetName == "sheetName") { //Checking if active sheet is that we want to be
  var jobValuesRange = s.getRange("C8:C" + lastRow); //Getting range that is only one column
  var jobValues = jobValuesRange.getValues(); //Getting values from only one column

    for (var i = 0; i < jobValues.length + 1; i++) { //Looping through all array values
     if (jobValues[i] == "") { 
       //Here is MAGIC where you must add brackets to create two dimension array.
       //Array should be like with lot of rows like first dimension and second dimension,
       //like one cell in that row. 
       jobValues[i] = [jobValues[i - 1] + " night"]; 
     }
    }
    jobValuesRange.setValues(jobValues); // Here you are just updating old values to new values
  }
}

Answer 2

setValues() and getValues() always use 2 dimension arrays, even if the range is only 1 row high, so you should use:

LINE 212     sheet_IndataTabell.getRange(1, 1, 1, 2).setValues([IndataTable_Temp]) 

(pay attention to the pair of brackets I added.)

Also, even if it's not an issue, you don't need to define the size of the array before using Range.getValues(), just drop the (NumberRows_Cal,NumberCols_Cal) you put as parameters,

var CalendarArray = new Array() 

is sufficient.

EDIT : about your comment : please add these two Logger.log in your code at line 225, when you get your 2 arrays, one of which not useable in setValues() and you'll see what happend :

    Logger.log(IndataTable);//this one contains JS objects between {}
    Logger.log(IndataTable_Temp);// this one contains array elements between [[]] 
  //Writes the array content to the sheet
  sheet_IndataTabell.getRange(1, 1, i, 11).setValues(IndataTable_Temp);

EDIT2 : I made a small change to the end of your script, now data are pure strings and array is real 2D (watch the brackets again):

  //Reconstruction the Array, I do not know why this is needed
  var IndataTable_Temp = new Array();
  for (i=0; i<IndataTable.length; i++) {
//  Logger.log(typeof(IndataTable[i][0]))
    IndataTable_Temp.push([
      IndataTable[i][0],
      IndataTable[i][1],
      IndataTable[i][2],
      IndataTable[i][3],
      IndataTable[i][4],
      IndataTable[i][5],
      IndataTable[i][6],
      IndataTable[i][7],
      IndataTable[i][8],
      IndataTable[i][9],
      IndataTable[i][10]
    ]);
  }
    Logger.log(IndataTable)
    Logger.log(IndataTable_Temp)
  //Writes the array content to the sheet
  sheet_IndataTabell.getRange(1, 1, IndataTable_Temp.length, IndataTable_Temp[0].length).setValues(IndataTable_Temp);

  //Sets timestamp
  sheet_IndataTabell.getRange("L1").setValue(Date());