Script copies row data to another sheet, is only partially working

Question

I have a script to move a row of data in an employee schedule spreadsheet (thanks to help from the Sheets reddit) it is working but has stopped finding one part of the data.

Spreadsheet here: https://docs.google.com/spreadsheets/d/10yLJ_NyFasGhlRt2LsXO2CC804Zf5JLvwUisgytQOhM/edit?usp=sharing

Once the Finished column 63 is set to "YES" it copies the data (only from the required columns), locates the employee's name based on whose week is marked "x" and moves them to the Finished Sheet.

The code used to work fully but now it isn't finding "x" or grabbing the employee's name. I've had to update the column numbers in the script a few times as we've gotten more employees, it's possible I've broken it doing that.

(Any blank columns do have data in my real spreadsheet I've just left them blank here as it's not relevant to the script)

Would really appreciate any advice! Thank you!

[Full code in spreadsheet]

  if (ecol == 63 && ssh.getName() == 'SCHEDULE' && rng.getValue() == 'YES') { //if Schedule!63 is YES
    var data = []; //output data
    var rowN = rng.getRow();
    var rowV = ssh.getRange(rowN,1,1,63).getDisplayValues();
    var row = rowV[0]; //get edited row
    var colX = row.indexOf('x')+1; //find X
    var offset = colX - ((colX-2) % 5 ) // offset to the first column 
    var emp = ssh.getRange(1,offset).getValue(); //get employee name
    var dd = Utilities.formatDate(new Date(), "GMT+10", "dd/MM/yyyy") //generate date finished
    data.push(row[1],row[2],row[3],dd,row[5],emp); //get row
    dsh.appendRow(data); //move row
    ssh.deleteRow(e.range.rowStart); //delete from schedule
  }
}

Have you added any columns to the left of the employee columns? — Cooper

Cooper Cooper · Accepted Answer · 2019-05-10T03:12:45

I believe this var offset = colX - ((colX-2) % 5 ) // offset to the first column

should be this var offset = colX - ((colX+2) % 5 ) // offset to the first column

This is what's causing you to not find employees because the employees are in a merge group of cells which always puts it's value in the left most cell for horizontal merging.

Script copies row data to another sheet, is only partially working

1 Answers