请参见下面的代码,我要实现的目标是从一张工作表中提取特定的数据列并将其插入新的工作表中。该脚本工作到一定程度,但是它只是在所有列中设置名字值,我很困惑,可以使用一些指导。
function obtainUniqueId() {
// Get Active spreadsheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet();
// Get payments sheet.
var getSheet = sheet.getSheetByName("Payments");
// Get last row from payments sheet.
var row = sheet.getLastRow();
// Get Client details sheet (not yet inserted).
var newSheet = sheet.getSheetByName("Client Details");
// Get first name from column C.
var firstName = getSheet.getRange('C' + row).getValue();
// Get last name from column D.
var lastName = getSheet.getRange('D' + row).getValue();
// Get email from column E.
var email = getSheet.getRange('E' + row).getValue();
// Get address from column F.
var address = getSheet.getRange('F' + row).getValue();
// Get town from column G.
var town = getSheet.getRange('G' + row).getValue();
// Get postcode from column H.
var postCode = getSheet.getRange('H' + row).getValue();
// Get reference from column L.
var reference = getSheet.getRange('L' + row).getValue();
// Get payment ID from column N.
var paymentId = getSheet.getRange('N' + row).getValue();
// Iterate through length of payment ID.
for (unique = 0; unique < paymentId.length; unique++){
// Slice payment ID at index 20 and 26.
var uniqueId = paymentId.slice(20, 26);
}
var clientData = [[firstName],[lastName],[email],[address],[town],[postCode],[reference],[uniqueId]];
for(var inputData = 0; inputData < clientData.length; inputData++) {
newSheet.getRange("A2:H2").setValue(clientData);
inputData++;
}
}
请参阅下面在下面使用的新行代码,它返回以下错误:无法转换。
// Array of client information.
var clientData = [[firstName],[lastName],[email],[address],[town],[postCode],[reference],[uniqueId]];
// Iterate through length of array.
for(var inputData = 0; inputData < clientData.length; inputData++){
// Get range
var clientInput = newSheet.getRange(2,1,1, clientData);
clientInput.setValues(clientData);
}
您仅在单个单元格中设置值,即 A2, H2
newSheet.getRange("A2:H2").setValue(clientData);
相反,我们需要在不同的单元格中设置值,如下所示:
var clientData = [[firstName],[lastName],[email],[address],[town],[postCode],[reference],[uniqueId]];
newSheet.getRange(2, 1, 1, clientData.length).setValues(clientData);
参考:https : //developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows,-numcolumns
https://developers.google.com/apps-script/reference/spreadsheet/range.html#setvalues值
谢谢,这有所帮助,但是,我现在遇到此错误:找不到方法getRange(number,number,number,number,object)。(第34行,文件“代码”)。新代码如下所示:var clientInput = newSheet.getRange(2,1,1,8,clientData).setValues(clientData);
@APStevens您将为getRange提供5个参数。这行不通。更改此:
var clientInput = newSheet.getRange(2,1,1,8, clientData).setValues(clientData);
这var clientInput = newSheet.getRange(2,1,1,clientData.length).setValues(clientData);
是在这个答案解释。我试过了,得到了无法转换的错误?
您可以使用正在尝试的代码更新您的问题吗?
问题已更新:)