温馨提示:本文翻译自stackoverflow.com,查看原文请点击:google apps script - How do I input an array into columns?
google-apps-script

google apps script - 如何将数组输入列?

发布于 2020-03-31 23:27:46

请参见下面的代码,我要实现的目标是从一张工作表中提取特定的数据列并将其插入新的工作表中。该脚本工作到一定程度,但是它只是在所有列中设置名字值,我很困惑,可以使用一些指导。

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);

}

查看更多

提问者
APStevens
被浏览
128
Umair Mohammad 2020-01-31 19:49

您仅在单个单元格中设置值,即 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值