google-apps-script multiple criteria writing over headers

Question

I have taken a bit of script from Serge which is great (original link here. I have added in a second criteria to exclude certain rows and it works great except, if there is not header in the sheet being copied to, it will not work (error: "The coordinates or dimensions of the range are invalid.") and if I enter a header or some other data, it overwrites it. Can anyone assist please? I have also found that is there is no match to the criteria I get following message "TypeError: Cannot read property "length" from undefined."

Also, what change would I need to make to change the cell 'dataSheetLog[i][12]' to the status variable, i.e. "COPIED" after I have copied it across. I have tried writing a setValue line but it is obviously the wrong instruction for that syntax.

Code is:

    {
    var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheetLog = Spreadsheet.getSheetByName("LOG");
    var sheetMaint = Spreadsheet.getSheetByName("MAINTENANCE");
    var Alast = sheetLog.getLastRow();
    var criteria = "08 - Maintenance"
    var status = "COPIED"
    var dataSheetLog = sheetLog.getRange(2,1,Alast,sheetLog.getLastColumn()).getValues();
    var outData = [];

    for (var i in dataSheetLog) {
    if (dataSheetLog[i][2]==criteria && dataSheetLog[i][12]!=status){
    outData.push(dataSheetLog[i]);
    }
    }
sheetMaint.getRange(sheetMaint.getLastRow(),1,outData.length,outData[0].length).setValues(outData);
}

ScampMichael · Accepted Answer · 2015-04-13T16:21:47.287

In:

sheetMaint.getRange(sheetMaint.getLastRow(),1,outData.length,outData[0].length).setValues(outData);

getLastRow() refers to the last occupied row and should be ,getLastRow() + 1,to keep from overwriting your headers and other problems.

Edited:

      {
    var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheetLog = Spreadsheet.getSheetByName("LOG");
    var sheetMaint = Spreadsheet.getSheetByName("MAINTENANCE");
    var Alast = sheetLog.getLastRow(); // Log
    var criteria = "08 - Maintenance"
    var status = "COPIED"
    var dataSheetLog = sheetLog.getRange(2,1,Alast,sheetLog.getLastColumn()).getValues(); //Log
    var dataSheetLogStatusRange = sheetLog.getRange(2,13,Alast,1); //Log
    var dataSheetLogStatus = dataSheetLogStatusRange.getValues();  //Log
    var outData = [];

    for (var i =0; i <  dataSheetLog.length; i++) {
    if (dataSheetLog[i][2]==criteria && dataSheetLog[i][12]!=status){
      outData.push(dataSheetLog[i]);
      dataSheetLogStatus[i][0] = "COPIED";
    }

    }
    if(outData.length > 0) {    
     sheetMaint.getRange(sheetMaint.getLastRow() + 1,1,outData.length,outData[0].length).setValues(outData);
     dataSheetLogStatusRange.setValues(dataSheetLogStatus);
    }
}
}

what change would I need to make to change the cell 'dataSheetLog[i][12]' to the status variable, i.e. "COPIED" after I have copied it across.

You were trying to update the value in the array that was extracted from the sheet and not the sheet itself. As arrays are zero based and spreadsheets are not, to translate, +1 must be added to array row and column indices. I am assuming status is in column M of your sheet.

Thanks ScampMichael, the header fix works great. As for the other, I kind of knew I was trying to alter something I couldn't alter so thank you for the explanation. When I added the line in though it isn't writing to each line. In the sheet I am playing with I have 5 lines out of 15 which should be copied, but only the fourth line gets "COPIED" entered in the cell. — witham, Apr 10 '15 at 08:07
The problem may have been due to the script executing faster than the sheet could be updated. Be that as it may, I was being lazy. It was not the correct way to do things. Sheets should almost never be updated from inside a loop. I have created a new array for status, updated it, and then replaced the existing range with the updated array as it should have been done in the first place. Sorry for misleading you. Let me know if all works OK. — ScampMichael, Apr 10 '15 at 15:28
Thanks again @ScampMichael, this time it is throwing an error "TypeError: Cannot read property "length" from undefined" on the line sheetMaint.getRange(sheetMaint.getLastRow() + 1,1,outData.length,outData[0].length).setValues(outData);<\CODE> — witham, Apr 13 '15 at 07:25

google-apps-script multiple criteria writing over headers

1 Answers1