String ranges in Google Apps Script

Question

I have a sheet that I need to get values from and set in a new sheet, one of these values requires me to slice string value at indices 20,26. I can achieve this using .getValue().slice(20,26) and .setValue(). However, the same data is set in all rows, which is to be expected since I am only getting a single cell of data. To resolve this I tried a for loop which failed.

When I attempt .getValues().slice(20,26) it returns an empty array which throws the following error: The number of rows in the data does not match the number of rows in the range. The data has 0 but the range has 20.

My code is below and I welcome any guidance.

function pj() {
 // Get Active spreadsheet.
   var sheet = SpreadsheetApp.getActiveSpreadsheet();
 //Get last row processed and update
   var startRow =sheet.getSheetByName("Index").getRange("A1").getValue();
   startRow++;
// Get payments sheet.
var paymentSheet = sheet.getSheetByName("Payments");
var clientSheet = sheet.getSheetByName("Client Details");
var lastRow = paymentSheet.getLastRow();
//Inputs
var firstName = paymentSheet.getRange(startRow, 3, (lastRow-startRow+1), 1).getValues();
var lastName= paymentSheet.getRange(startRow, 4, (lastRow-startRow+1), 1).getValues();
var paymentId = paymentSheet.getRange(startRow, 14, (lastRow-startRow+1), 1).getValues().slice(20,26);
}

//Outputs
clientSheet.getRange(2,1,(lastRow-startRow+1),1).setValues(firstName);
clientSheet.getRange(2,8, (lastRow-startRow+1),1).setValues(paymentId);



//Update the last row processed
sheet.getSheetByName('Index').getRange("A1").setValue(lastRow);
}

Please see image of the sheet I am working with, as you will see column N contains a payment ID, which holds a unique identifier for the client that I need to extract in order to upload this data into Salesforce. It seems the issue is that Logger.log is reporting the string correctly as it goes through the array, but is reporting the length as 1. I've tried changing the format of that column various ways, Text, Number etc but it doesn't seem to make any difference to the reported length. I'm assuming this is why the split function isn't working, but I'm not sure why it's happening?Sheet

Can you share a copy of the spreadsheet you are working on (free of sensitive information) as well as the outcome you expect? — Iamblichus
getValues returns an array of arrays, so you need to index into it. Outer array is rows, inner array is columns. As can be expected, it is 0-based. — tehhowch

Cooper Cooper · Accepted Answer · 2020-02-12T16:41:26

I guessed at a lot of this but it should look similar to this:

function pj() {
  var ss=SpreadsheetApp.getActive();
  var isht=ss.getSheetByName("Index");
  var startRow=isht.getRange("A1").getValue()+1;
  var psht=ss.getSheetByName("Payments");
  var csht=ss.getSheetByName("Client Details");
  var lastRow=psht.getLastRow();
  var prg=psht.getRange(startRow,1,psht.getLastRow()-startRow+1,14);
  var pvals=prg.getValues();
  pvals.forEach(function(r,i){
    csht.getRange(i+2,1).setValue(r[2]);
    csht.getRange(i+2,2).setValue(r[3]);
    csht.getRange(i+2,8).setValue(r[13].slice(20,26));
  });
  isht.getRange("A1").setValue(csht.getLastRow());
}

String ranges in Google Apps Script

1 Answers