我正在嘗試編寫一個 Apps 腳本,運行時(從觸發器或自定義菜單)它將:
我目前的問題是:
因此,我需要知道如何根據未附加的行數阻止腳本運行,並且不包括“附加”列 (F),並且僅在附加後標記每一行。
這是當前的腳本:
function appendToRecords() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
/** set the name of the SOURCE_SHEET sheet */
var sourceSheet = ss.getSheetByName("NEWDATA");
var startRow = 2; // First row of data to process
var numRows = sourceSheet.getLastRow()-1; // Number of rows to process
var dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
var sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
var APPENDED = 'APPENDED';
for (var i = 0; i < sourceData.length; ++i) {
var row = sourceData[i];
if (row[5] != APPENDED) { // To prevent sending duplicates, check that not already APPENDED
/** set the name of the TARGET_SHEET */
var targetSheet = ss.getSheetByName("ALLDATA")
/** Append NEWDATA to ALLDATA */
var lastRow = targetSheet.getLastRow();
targetSheet.getRange(lastRow + 1, 1, 10, 6)
.setValues(sourceData);
sourceSheet.getRange(startRow+i,6).setValue("APPENDED"); // Add APPENDED indication to end of row
}}}
這是文件的鏈接:
https://docs.google.com/spreadsheets/d/1gOgvvZlCQgZi837sapZ3S8M8m6I98VFnGQwPiTCSY-I/edit?usp=sharing
任何幫助將不勝感激,謝謝。
查看您的代碼,我看到的最大問題是當您在目標表上設置值時,您正在執行sourcedata
而不是row
. 我通過隔離一些常量並使其更具動態性來清理您的代碼..最終您應該嘗試使用數組在過程結束時設置值,而不是使用逐行更新,但為了回答您的問題,嘗試這個:
/** @OnlyCurrentDoc*/
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("NEWDATA");
const targetSheet = ss.getSheetByName("ALLDATA");
const startRow = 2; // First row of data to process
const APPENDED = 'APPENDED';
function appendToRecords() {
const numRows = sourceSheet.getLastRow() - 1; // Number of rows to process
const dataRange = sourceSheet.getRange(startRow, 1, numRows, sourceSheet.getLastColumn()); // Fetch the range of cells being used
const sourceData = dataRange.getValues(); // Fetch values for each row in the Range.
const lastColumn = sourceData[0].length;
for (var i = 0; i < sourceData.length; ++i) {
var row = sourceData[i];
if (row[lastColumn-1] != APPENDED) {
///you should not do this line by line, but all at oncw with an array
row[lastColumn-1] = APPENDED;
var lastRow = targetSheet.getLastRow();
targetSheet.getRange(lastRow + 1, 1, 1, row.length).setValues([row]);
sourceSheet.getRange(startRow + i, lastColumn).setValue("APPENDED");
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句