javascript - 我需要帮助拆分数组

Question

我很难编辑这个脚本。它查看一个选项卡，拉出所有符合条件的行并将它们粘贴到另一个选项卡中。下面的脚本有效，但不太适合我的需要。它正在粘贴整行，我只需要 A、B 和 C 列。理想情况下，我需要将数据分别复制到 A、D 和 E 列。谢谢你的帮助！

    function Migrate2() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('OnBoard');
    var targetSheet = ss.getSheetByName('NewHire');
    var val = sheet.getRange('a3:l').getValues();
    var headers = val.shift();
    var arr = [],
        rowsToWriteBack = [];

    rowsToWriteBack.push(headers);
    val.forEach(function (r, i) {
        r[7] == 'COMPLETE'&& r[11] == 'N' ? arr.push(r) : rowsToWriteBack.push(r)});

    if (arr.length > 0) {
        targetSheet.getRange(targetSheet.getLastRow() + 1, 1, arr.length, arr[0].length).setValues(arr);

    }
   }

标签： javascriptarraysspreadsheet

Answer 1

尝试这个

function Migrate2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('OnBoard');
    var targetSheet = ss.getSheetByName('NewHire');
    var val = sheet.getRange('a3:l').getValues();
    var headers = val.shift();
    var arr = [],
    rowsToWriteBack = [];

    rowsToWriteBack.push(headers);

    val.forEach(function (r, i) {
            r[7] == 'COMPLETE'&& r[11] == 'N' ? 
                arr.push([r[0],r[1],r[2]]) : 
                rowsToWriteBack.push([r[0],r[1],r[2]])
    });

        if (arr.length > 0) {
                            for(var i = 0; i < arr.length; i++) {
                                targetSheet.getRange(targetSheet.getLastRow() + i + 1, 1, 1, 1).setValues(arr[i][0]);
                                targetSheet.getRange(targetSheet.getLastRow() + i + 1, 4, 1, 1).setValues(arr[i][1]);
                                targetSheet.getRange(targetSheet.getLastRow() + i + 1, 5, 1, 1).setValues(arr[i][2]);
                            }
        }
    }
}

如果无法访问您正在工作的环境，很难进行测试，但我认为上述方法应该可行。祝你好运！

这是（希望）修复了令人震惊的问题的更新版本：

function Migrate2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('OnBoard');
    var targetSheet = ss.getSheetByName('NewHire');
    var val = sheet.getRange('a3:l').getValues();
    var headers = val.shift();
    var arr = [],
    rowsToWriteBack = [];

    rowsToWriteBack.push(headers);

    val.forEach(function (r, i) {
            r[7] == 'COMPLETE'&& r[11] == 'N' ? 
            arr.push([r[0],r[1],r[2]]) : 
            rowsToWriteBack.push([r[0],r[1],r[2]])
    });

        if (arr.length > 0) {
            for(var i = 0; i < arr.length; i++) {
                var addToRow = targetSheet.getLastRow() + i + 1;

                targetSheet.getRange(addToRow, 1, 1, 1).setValues(arr[i][0]);
                targetSheet.getRange(addToRow, 4, 1, 1).setValues(arr[i][1]);
                targetSheet.getRange(addToRow, 5, 1, 1).setValues(arr[i][2]);
            }
        }
    }
}

尝试 #3 以防止跳行

    function Migrate2() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName('OnBoard');
        var targetSheet = ss.getSheetByName('NewHire');
            var startingRow = targetSheet.getLastRow() + 1;
        var val = sheet.getRange('a3:l').getValues();
        var headers = val.shift();
        var arr = [],
        rowsToWriteBack = [];

        rowsToWriteBack.push(headers);

        val.forEach(function (r, i) {
                r[7] == 'COMPLETE'&& r[11] == 'N' ? 
                arr.push([r[0],r[1],r[2]]) : 
                rowsToWriteBack.push([r[0],r[1],r[2]])
        });

            if (arr.length > 0) {
                for(var i = 0; i < arr.length; i++) {
                    var addToRow = startingRow + i;

                    targetSheet.getRange(addToRow, 1, 1, 1).setValues(arr[i][0]);
                    targetSheet.getRange(addToRow, 4, 1, 1).setValues(arr[i][1]);
                    targetSheet.getRange(addToRow, 5, 1, 1).setValues(arr[i][2]);
                }
            }
        }
    }