CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Akaishen
Akaishen

Reputation: 418

Get index of last column of each row in a Google Spreadsheet

I have a Range of all rows and columns in my Google Spreadsheet. I'm looping through each row. If my conditional statement is true, I need to append some data to the row in the furthest to the right column.

Any pointers as to how I can achieve this? Thanks!

Upvotes: 1

Views: 2304

Answers (2)

DavidF
DavidF

Reputation: 1345

The key point is to get the range data into an array; then iterate through the array; then write the array back. Acting on spreadsheet objects like cells is relatively inefficient.

function writeRows() {
  // This is the named range you have.
  var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("myRange");

  // Get the data and iterate through it rather than iterate through rows.
  var dataarray = range.getValues();

  // For each row.
  for (var rowpos = 0; rowpos < dataarray.length; rowpos++) {
    // Get the row data as array.
    var rowdata = dataarray[rowpos];
    Logger.log(rowdata.length);

    // Update last column in this row.
    dataarray[rowpos][rowdata.length - 1] = "value to set";
  }
  // Now write back the array values into the range.
  range.setValues(dataarray);
};

Upvotes: 2

Sam
Sam

Reputation: 144

this i what I use to append a row to a spreadsheet:

  var ss = SpreadsheetApp.openById('spredsheetID').getSheets()[0];
  var range = ss.getRange(ss.getLastRow()+1, 1, 1, 2);
  var values = [[new Date(), mystring]]; 
  range.setValues(values);

it updates the first 2 columns ...

Upvotes: -1

Related Questions