Choose Multiple Columns in Google Sheets Script

Question

I have a dataset of jobs for a gardening company with 50 or so columns.

status   #    ID     Date       Name   Frequency    Height   Weight   DOB...
☐        4    340    09/06/20   Jack   Once Off     175      100      11/1/60...
☐        1    543    22/05/20   Sarah  Weekly       170      201      12/2/87...
☐        3    121    01/05/20   Emily  Fortnightly  172      150      6/6/90...
☐        3    577    11/06/20   Peter  Once Off     165      165      31/1/89...

I have a custom script that recreates a new row when the checkbox is selected for weekly and fortnightly jobs and increases the # field by 1 and adds the appropriate date in the future for the date field. I also want it to copy all other data in the original row into the new row.

Currently it will copy up to column 9 but I have 50 columns that need the original data copied over. Is it possible to use more efficient code rather than using the below 50 times?

sheet.getRange(selectedRow,5).getValue(),
sheet.getRange(selectedRow,6).getValue(),
sheet.getRange(selectedRow,7).getValue(),
sheet.getRange(selectedRow,8).getValue(),
sheet.getRange(selectedRow,9).getValue(),

Here is the script:

function onEdit() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var checkbox = sheet.getActiveRange().getValue();
  var selectedRow = sheet.getActiveRange().getRow();
  var selectedFreq = sheet.getRange(selectedRow, 6).getValue();
  
  //Run if selected cell has a checkbox and ticked
  while(checkbox == true){ 
    addValues(selectedRow, sheet, selectedFreq);
    break;
  }
}

//Function to process values based on frequency
function addValues(selectedRow, sheet, selectedFreq){ 
  var number = sheet.getRange(selectedRow,2).getValue();
  var date = new Date(sheet.getRange(selectedRow,4).getValue());

  if(selectedFreq == "Fortnightly"){
    //Insert a new row after the ticked checkbox, setup a new date with 14 days (or 2 weeks), increments the # with 1 & the rest of the data are copied
    var newDate = new Date(date.setDate(date.getDate()+14));
    sheet.appendRow(["",number+1,sheet.getRange(selectedRow,3).getValue(),newDate,
    sheet.getRange(selectedRow,5).getValue(),
    sheet.getRange(selectedRow,6).getValue(),
    sheet.getRange(selectedRow,7).getValue(),
    sheet.getRange(selectedRow,8).getValue(),
    sheet.getRange(selectedRow,9).getValue()]);
    sheet.getRange(sheet.getLastRow(),1).insertCheckboxes();
    sheet.getRange(sheet.getLastRow(),4).setValue(sheet.getRange(sheet.getLastRow(),4).getValue()).setNumberFormat("dd/MM/yy");
  }

  if(selectedFreq == "Weekly"){
    //Insert a new row after the ticked checkbox, setup a new date with 7 days (1 week), increments the # with 1 & the rest of the data are copied
    var newDate = new Date(date.setDate(date.getDate()+7));
    sheet.appendRow(["",number+1,sheet.getRange(selectedRow,3).getValue(),newDate,
    sheet.getRange(selectedRow,5).getValue(),
    sheet.getRange(selectedRow,6).getValue(),
    sheet.getRange(selectedRow,7).getValue(),
    sheet.getRange(selectedRow,8).getValue(),
    sheet.getRange(selectedRow,9).getValue()]);
    sheet.getRange(sheet.getLastRow(),1).insertCheckboxes();
    sheet.getRange(sheet.getLastRow(),4).setValue(sheet.getRange(sheet.getLastRow(),4).getValue()).setNumberFormat("dd/MM/yy");
  }
}

Ron M Ron M · Accepted Answer · 2021-06-14T16:32:47

I suggest that you use Sheet.getRange(row, column, numRows, numColumns) and Range.getValues() to get the row values with multiple columns.

Sample:

var values = sheet.getRange(selectedRow,1,1,50).getValues();
values[0][1] = number+1;
sheet.appendRow(values.flat());

With this, you can get the values of your selected row, from column 1 to column 50.
Then you can just update specific index in your array values depending on your preference. Like values[0][1] = number+1
Lastly, you can use your array values in Sheet.appendRow(). You can use array.flat() to convert 2-d array to 1-d array

(UPDATE)

Your code must be something like this:

//Function to process values based on frequency
function addValues(selectedRow, sheet, selectedFreq){ 

  var rowValues = sheet.getRange(selectedRow,1,1,50).getValues().flat();  
  var date = new Date(sheet.getRange(selectedRow,4).getValue());

  if(selectedFreq == "Fortnightly"){
    //Insert a new row after the ticked checkbox, setup a new date with 14 days (or 2 weeks), increments the # with 1 & the rest of the data are copied
    var newDate = new Date(date.setDate(date.getDate()+14));

    //Update column A,B and D
    rowValues[0] = "";
    rowValues[1] += 1;
    rowValues[3] = newDate;
    sheet.appendRow(rowValues);
    sheet.getRange(sheet.getLastRow(),1).insertCheckboxes();
    sheet.getRange(sheet.getLastRow(),4).setValue(sheet.getRange(sheet.getLastRow(),4).getValue()).setNumberFormat("dd/MM/yy");
  }
  
}

I get the selected row values from column 1 to 50. Then use array.flat() to change the result from 2-d array to 1-d array
I changed column A, B and D based on your original code
appended the updated data in the sheet

Choose Multiple Columns in Google Sheets Script

1 Answers

(UPDATE)