无法用当前代码填充数组 [英] 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屋！