从数组到setValues给出:“不能转换为”。 [英] From Array to setValues gives: "Cannot convert to ."
问题描述
背景。我想从周格式中更改时间表(每行显示7天,没有日期仅在yyww(例如1225)中有一周可用。在另一表中,一列列出周,另一列列出日期。
方法。我将这两张表分成两个数组,将第三个数组打包,并将其设置为第三个数据表。
问题。这行提供了错误信息:无法转换为。
pre $ sheet_IndataTabell.getRange(1,1,IndataTable.length + 1,7).setValues(IndataTable);
来源。
您可以看到文档此处和下面的完整功能:
函数UpdateTable(){
变种SS = SpreadsheetApp.getActiveSpreadsheet();
变种sheet_Indata = ss.getSheetByName( INDATA);
var sheet_IndataTabell = ss.getSheetByName(Indata_Tabell);
var sheet_Calendar = ss.g etSheetByName( KALENDER);
//将表获取到数组
var Indata = sheet_Indata.getDataRange();
var CalendarTable = sheet_Calendar.getDataRange();
//获取Indata中的值到数组
var NumberRows = Indata.getLastRow();
var NumberCols = Indata.getLastColumn();
//Browser.msgBox(NumberRows ++ NumberCols);
var IndataArray = new Array(NumberRows,NumberCols);
var IndataArray = Indata.getValues();
//创建一个数组来存储结果
var IndataTable = new Array(((NumberCols-1)*(NumberRows-1)),7); (NumberCols-1)*(NumberRows-1)(+(NumberCols-1)*(NumberRows-1)+)。);
//Browser.msgBox(\"FirstInstataTable定义为:
//获取日历中的数值到数组
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(); (j = 3; j <10; j ++){b = b; b
IndataTable [i * 7 + j-3-7] = {};
//添加用户
IndataTable [i * 7 + j-3-7] [0] = IndataArray [i] [1];
//添加周数
var Vecka = IndataArray [i] [2];
IndataTable [i * 7 + j-3-7] [1] = Vecka;
//添加小时
IndataTable [i * 7 + j-3-7] [2] = IndataArray [i] [j];
//添加项目
IndataTable [i * 7 + j-3-7] [3] = IndataArray [i] [10];
//添加日期
var Dag =(j-2);
IndataTable [i * 7 + j-3-7] [4] = Dag;
//获取日历中的日期
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());
解决方案 setValues()
和 getValues()
总是使用2维数组,即使范围只有1行高,使用:
LINE 212 sheet_IndataTabell.getRange(1,1,2).setValues([IndataTable_Temp])
(请注意我添加的一对括号。)
另外,即使这不是问题,在使用 Range.getValues()
之前,您不需要定义数组的大小,只需将(NumberRows_Cal,NumberCols_Cal)
您作为参数,
var CalendarArray = new Array ()
已足够。
编辑:关于你的评论:请在第225行的代码中添加这两个Logger.log,当你得到你的两个数组,其中一个在setValues()中不可用,你会看到发生了什么:
Logger.lo g(IndataTable); //这个包含{}
之间的JS对象Logger.log(IndataTable_Temp); //这个包含[[]]
之间的数组元素//将数组内容写入sheet
sheet_IndataTabell.getRange(1,1,i,11).setValues(IndataTable_Temp);
编辑2:我对脚本的末尾做了一些小改动,现在数据是纯字符串和数组是真正的2D(再次看括号):
//重建数组,我不知道为什么需要
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)
//数组内容写入到片材
sheet_IndataTabell.getRange(1,1, IndataTable_Temp.length,IndataTable_Temp [0] .length).setValues(IndataTable_Temp);
//设置时间戳
sheet_IndataTabell.getRange(L1)。setValue(Date());
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());
}
解决方案 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());
这篇关于从数组到setValues给出:“不能转换为”。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!