为什么只有最后一个源工作表的信息发布在目标工作表上？

Question

我正在尝试将来自多个源电子表格的信息发布到谷歌表格中的目标电子表格。当我测试Logger.log((时，一切都应该正常工作，但是只有最后一个源电子表格的信息发布在目标电子表格上。我做错了什么？

function retrieveData(ssA,ssB) {
/* Gets the source sheets' id values typed manually in a range of cells on active sheet*/
var ssA = SpreadsheetApp.getActiveSheet().getSheetValues(2, 3, 4,1);
/*Gets the source sheets'names typed manually in a range of cells on active sheet*/
var sourceSpreadsheetName = SpreadsheetApp.getActiveSheet().getSheetValues(2, 4, 4, 1);
/* loops through an array containing source sheets' ids*/
ssA.forEach(function(ssA){
var sss = SpreadsheetApp.openById(ssA)
/*loops through an array containing source sheets' names */
sourceSpreadsheetName.forEach(function(sourceSpreadsheetName){
var ss = sss.getSheetByName(sourceSpreadsheetName)
if(ss != null) {
/* gets range on each source sheet */
var SRange = ss.getRange(2, 1, 50, 2);
/*gets A1 notation on each source sheet*/
var A1Range = SRange.getA1Notation();
/*gets cell values on each source sheet */
var SData = SRange.getValues();
/* target sheet id */
var ssB = '1Dp1dOcwaup184mt1zWU-lcA5Nw-jFguWrr_H2hrtKSg';
var tss = SpreadsheetApp.openById(ssB);
var ts = tss.getSheetByName('Customers need to contact');
/* sets values retrieved from source sheets on target sheet*/
ts.getRange(A1Range).setValues(SData); 
}
})
})
}

Answer 1

修改添加日志的代码，如下所示：

function retrieveData(ssA,ssB) {
/* Gets the source sheets' id values typed manually in a range of cells on active sheet*/
var ssA = SpreadsheetApp.getActiveSheet().getSheetValues(2, 3, 4,1);
/*Gets the source sheets'names typed manually in a range of cells on active sheet*/
var sourceSpreadsheetName = SpreadsheetApp.getActiveSheet().getSheetValues(2, 4, 4, 1);
/* loops through an array containing source sheets' ids*/
ssA.forEach(function(ssA){
var sss = SpreadsheetApp.openById(ssA)
/*loops through an array containing source sheets' names */
sourceSpreadsheetName.forEach(function(sourceSpreadsheetName){
var ss = sss.getSheetByName(sourceSpreadsheetName)
if(ss != null) {
Logger.log('spreadsheet and sheet: '+sss.getName()+ss.getName());
/* gets range on each source sheet */
var SRange = ss.getRange(2, 1, 50, 2);
/*gets A1 notation on each source sheet*/
var A1Range = SRange.getA1Notation();
Logger.log('a1 notation: '+A1Range);
/*gets cell values on each source sheet */
var SData = SRange.getValues();
/* target sheet id */
var ssB = '1Dp1dOcwaup184mt1zWU-lcA5Nw-jFguWrr_H2hrtKSg';
var tss = SpreadsheetApp.openById(ssB);
var ts = tss.getSheetByName('Customers need to contact');
/* sets values retrieved from source sheets on target sheet*/
ts.getRange(A1Range).setValues(SData); 
}
})
})
}

您将看到您从每个源工作表中检索范围A2:B51并将其粘贴到目标工作表的范围A2:B51中。这意味着您将覆盖每个迭代步骤中的粘贴值。

例如，可以通过将

每个源表中的值附加到目标工作表来解决此问题

var lastRow=ts.getLastRow();
ts.getRange(lastRow,1,50,2).setValues(SData);