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());

}

Organdy

This is a very old question but I just had the same problem. user1460248 said: "That worked but it would be nice to understand why the first array is in the wrong format." I found that a null value had crept into my array, eg: [ ['2018-07-12', ['LUNCH '], 1.0], null, ['2018-07-13', 'Inbox, planning', 0.5] ] Google Apps Script was throwing the "Cannot convert Array to Object[][]." because of the null value. – Slattery

Answer 1

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());

Fleda

Thank you for answer, Serge. I tried that and it did not work, though. The array is already a 2D array (I guess) but some how in the wrong format. What I ended up doing was to loop through the array and created a new one using the format [[a,b,c],[d,e,f]]. That worked but it would be nice to understand why the first array is in the wrong format. Ps. Yes, I made some simplification now :) This is my first script ever ;) – Organdy

is your online copy up to date with the new script ? – Fleda

Also : don't hesitate to use Logger.log(variable name) to see for example if an array is 1D or 2D .... very useful ;-) – Fleda

Thank you Serge. The Logger.log() really made it easier to debug! Yes, the online copy is the developing one (to be production). Would you explain why the IndataTable JS objects? – Organdy

Your code is a bit long and complicated (the parts in swedish are really hard;-) but by placing logs at different points will let see what happens. Look at the 2 logs in my first edit :[{3=Hos kund, 2=0.0, 10=0.0, 1=1105.0... the form is 'index=value' each time. – Fleda

Thank you Serge. Your really helped! I will start using Logger.log() for sure. My first task will be to find out why the array is built in this way. Take care and thanks again. – Organdy

Thank you very much – Aguedaaguero

Answer 2

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
  }
}

Nyctaginaceous