CODEWITHSUNDEEP
google-apps-script
user11416172
user11416172

Reputation:

How do I input an array into columns?

Please see my code below, what I am trying to achieve is taking particular columns of data from one sheet and inserting it into a new sheet. The script works to a point however it is only setting the first name value across all columns, I am stumped and could use some guidance.

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++;
  }
}

See new line of code I have used below which returns the following error: Cannot convert.

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

}

Upvotes: 0

Views: 63

Answers (1)

Umair Mohammad
Umair Mohammad

Reputation: 4635

You're setting value in only single cell i.e. A2, H2

newSheet.getRange("A2:H2").setValue(clientData);

Instead we need to set values in different cells, something like this :

var clientData = [[firstName],[lastName],[email],[address],[town],[postCode],[reference],[uniqueId]];
newSheet.getRange(2, 1, 1, clientData.length).setValues(clientData);

Reference : https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows,-numcolumns

https://developers.google.com/apps-script/reference/spreadsheet/range.html#setvaluesvalues

Upvotes: 1

Related Questions