无法用当前代码填充数组 [英] Unable to populate an array with current code
问题描述
如果主表中的新数据丢失,我正在尝试检查目标工作表。如果是这样,它将新数据从主工作表中的3列复制到目标工作表。我在最后得到一个错误,因为我的数组未定义,我不确定如何解决问题。错误是TypeError:无法从undefined读取属性length。 (第27行,文件代码)
I'm attempting to check a destination sheet if new data in a master sheet is missing. If so it copies the new data from 3 columns in the master sheet to the destination sheet. I am getting an error at the end because my array is undefined and I'm unsure how to go about fixing the issue. The error is TypeError: Cannot read property "length" from undefined. (line 27, file "Code")
第27行是倒数第二行(.setValues(arr);)
Line 27 is the 2nd to last line (.setValues(arr);)
function Copy() {
var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
.getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (prevLastRow == undefined) {
prevLastRow = (sourceSheet.getLastRow() -1);
}
else {
var prevLastRow = PropertiesService.getScriptProperties.getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
return;
}
var range = sourceSheet.getRange(prevLastRow,
1,
lastRow - prevLastRow,
sourceSheet.getLastColumn()
);
var arr = [];
var rangeval = range.getValues()
.forEach(function (r, i, v) {
if (r[i][1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
});
var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
.getSheetByName('Sheet1');
destinationSheet.getRange(destinationSheet.getLastRow() +1, 1, arr.length, arr[0].length)
.setValues(arr);
PropertiesService.getScriptProperties.setProperty('lastRow', lastRow)
}
编辑:
我已经将第一个If语句更改为以下内容:
edit: I've changed the first If statement to the following:
if (prevLastRow == undefined) {
prevLastRow = 1;
}
else {
var prevLastRow = PropertiesService.getScriptProperties.getProperty('lastRow');
}
这会返回另一个电子表格上的值,但不会保存最后一行正如我在主代码的最后一行中所做的那样。
This returns the values on the other spreadsheet but it does not save the last row as I'm trying to do in the last line of the main code.
第二编辑:此代码有效,但不会复制最后一行:
2nd This code works, but doesn't copy the very last row:
function Copy() {
var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
.getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (PropertiesService.getScriptProperties().getProperty('lastRow') == undefined) {
prevLastRow = 1;
}
else {
var prevLastRow = PropertiesService.getScriptProperties().getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
return;
}
var range = sourceSheet.getRange(prevLastRow,
1,
lastRow - prevLastRow,
sourceSheet.getLastColumn()
);
var arr = [];
var rangeval = range.getValues()
.forEach(function (r, i, v) {
if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
});
var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
.getSheetByName('Sheet1');
destinationSheet.getRange(destinationSheet.getLastRow() +1, 1, arr.length, arr[0].length)
.setValues(arr);
PropertiesService.getScriptProperties().setProperty('lastRow', lastRow)
}
推荐答案
这是任何寻找类似东西的人的工作版本。 :)
Here is a working version for anyone looking for something similar. :)
我上次编辑的解决方案是将开始var range = ...的行改为lastRow - prevLastRow为(lastRow - prevLastRow)+1,它记录最后一行。
Solution from my last edit was to shift the line that begins "var range = ..." Changed lastRow - prevLastRow to (lastRow - prevLastRow)+1 and it records the last row.
function Copy() {
var sourceSheet = SpreadsheetApp.openById('1iyxDeeKoPdVwl15bXTOeorbHylw_i39BsM4NQnxDeiY')
.getSheetByName('Jobs Log');
var lastRow = sourceSheet.getLastRow();
if (PropertiesService.getScriptProperties().getProperty('lastRow') == undefined) {
prevLastRow = 1;
}
else {
var prevLastRow = PropertiesService.getScriptProperties().getProperty('lastRow');
}
if (lastRow <= prevLastRow) {
return;
}
var range = sourceSheet.getRange(prevLastRow,
1,
(lastRow - prevLastRow)+1 ,
sourceSheet.getLastColumn()
);
var arr = [];
var rangeval = range.getValues()
.forEach(function (r, i, v) {
if (r[1] == 'Amber') arr.push([v[i][0], v[i][3], v[i][2]]);
});
var destinationSheet = SpreadsheetApp.openById('1UXPyPmOcsLzyBXAzKax8oVVvUSRC8bfgLP2S7j2F9Yw')
.getSheetByName('Sheet1');
destinationSheet.getRange(destinationSheet.getLastRow(), 1, arr.length, arr[0].length)
.setValues(arr);
PropertiesService.getScriptProperties().setProperty('lastRow', lastRow)
}
这篇关于无法用当前代码填充数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!