以前复制的范围值被新复制的数据重写

Question

以前复制的范围值被新复制的数据重写我正在开发的函数有4个部分。

第1部分：将从范围"Ai:Pi"中的行"i"开始的一行数据复制到同一工作表上的不同位置——范围"A5:P5"(我称之为输入数据暂存范围)，其中i=行号，每次一个。工作良好

第2部分：来自暂存范围的数据然后被拉到另一个电子表格中进行处理。工作良好

第3部分：然后将另一个电子表格中的处理数据复制回原始电子表格——范围"T5-AA5"(我称之为处理数据暂存范围)。工作良好

第4部分：然后我只想将VALUES从范围"T5:AA5"复制到与输入数据相同的行"I"，这样要复制的范围就是"Ti:AAi"。现在正在工作。

我已经在Excel中测试了我想要做的事情，当我使用PASTE SPECIAL时，它运行得非常好。然而，在谷歌应用程序中，问题是，当值被复制到右侧的"I"行时，它们会被要复制的新数据范围所覆盖。我真的想解决第4部分的问题。

我的代码是：

函数getRowReturnValue()｛

// "Form Responses 1" is the sheet with the Forms Data.
// The "Simple loop testing" sheet was set up to test the development of teh Apps scripts.
// Spreadsheet Sources
var ss = SpreadsheetApp.getActiveSpreadsheet();
var processor = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1fuGpv7YsyM5sCAMC25UkpfEURm06fJjyieezY3CjMl4/edit")// This is the PROCESSOR spreadsheet where 

// Data source sheets in the source spreadsheets
// NOTE: IF YOU CHANGE THE SHEET NAME, THEN YOU NEED TO CHANGE TH SHEET NUMBER IN THE SQUARE BRACKETS BELOW FOR SOURCE and DESTINATION variables
// [0} = sheet 1 = "Form Responses 1"; [1] = "Test Sheet"; [2] = "Simple loop testing"
var spr = ss.getSheetByName("Form Responses 1")
var processorOutputData = processor.getSheetByName("MASTER OUTPUT"); 
var source_sheet = ss.getSheets()[0];           // This is the location sheet of the FORMS INPUT data. In this case the "[0]" indicates the position of the very first sheet in the source spreadsheet
var target_sheet = ss.getSheets()[0];           // This is the location sheet of the PROCESSED data from the processor. In this case the "[0]" indiates the position of the very first sheet in the 
// source spreadsheet

// Identify the number of rows of Forms Input data to be processed.  
var lastDataRow = spr.getLastRow();             // NOTE: This rule is identifies the last low number, ignoring blank rows in between. The disadvantage is that it looks at the WHOLE row and not just 
// the cells of the row into which the Forms Input data will occupy
Logger.log(lastDataRow);                    // CHECK
var NumbOfResponses = spr.getLastRow();         // CHECK
Logger.log(NumbOfResponses);                // CHECK
// Clear the content of the staging row
var clearStagingRow = spr.getRange(5,1,1,16);   
clearStagingRow.clearContent();

for (var i = 10; i<NumbOfResponses+1;i++) {
// Extract the input data VALUES, row by row, to be processed from the source sheet and put it into the INPUT STAGING area - which holds one row of data at a time.
var values = source_sheet.getRange(i,1,1,16); 
values.copyValuesToRange(target_sheet, 1, 16, 5, 5);               // Once the data is in the INOUT STAGING area, it is imported to the "MASTER OUTPUT" sheet in the processor spreadsheet.
        // This data is then processed and the resulting output data is posted in a range ready to be copied back into 
        // the "Forms responses 1" sheet
// Identify the source range from the "MASTER OUTPUT" processor sheet. Identify the target range in the PROCESSED STAGING range in the "Forms responses 1" sheet
var processedData = processorOutputData.getRange("d43:k43").getValues();            // Identify the range in the "MASTER OUTPUT" from where the data is to be sourced
var target_range = target_sheet.getRange("T5:AA5").setValues(processedData);                    // Identify the target range in the PROCESSED STAGING area where the input is to be put

// Copy PROCESSED OUTPUT data from OUTPUT STAGING range to the row alligned to the input data
var retValues = source_sheet.getRange(5,20,1,8);
retValues.copyValuesToRange(target_sheet, 20, 27, i, i);
// Clear out the data previously copied into the OUTPUT STAGING data
target_range.clearContent();
} 

}

Answer 1

我已经审查并重新设计了模型，幸运的是，简单性解决了这个问题。运行良好的代码是var source_range=source_sheet.getRange(i，1,1,16)；//确定数据来源的范围和位置var source_values=source_range.getValues()；//获取位置中的值var target_range=target_sheet.getRange(8,4,1,16)；//标识要在处理器中复制数据的范围和位置target_range=target_range.setValues(source_values)；//在处理器中复制目标范围和位置中的值

var targetoutput_range = target_sheet.getRange(38,4,1,8); // identify range and location of where the processed data is
var targetoutput_values = targetoutput_range.getValues(); // get the output values
var destoutput_range = source_sheet.getRange(i,18,1,8);   // identify the range and location of where the output data will be copied
destoutput_range = destoutput_range.setValues(targetoutput_values);  // copy the output values in the destination range and location

我在一个循环中运行上面的内容，它将输出值作为输入数据发布在同一行中。我遇到的问题是，随着inout行数的增加，处理数据所需的时间也在增加。我的答案是只评估新行，而不影响"旧"行。我的代码对此有效，但所花费的时间并没有缩短。因此，如果有关于如何缩短时间的建议，请推荐。