Auto Archive Schedule; Time-based Trigger

Question

I have been asked to create a live Google Sheets Spreadsheet to track the work schedule at our yard. I have no experience with a script but found out I could program my sheet instead of hiding formulas and it would yield a cleaner result. I have been able to make the sheet organize itself and I was able to make it Archive manually (onEdit). What I'm looking for is to have it automatically run the code at 1 am so when we arrive at work it archives based on a cell value in a certain column.

This is an example of my onEdit script that works, but when someone is trying to check off the "YES" column there is some lag and can cause the wrong cell to be checked, which I then manually correct.

function onEdit() {
  var sheetNameToWatch = "Schedule";

  var columnNumberToWatch = 28;
  var valueToWatch = "Yes";
  var sheetNameToMoveTheRowTo = "Archive";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

So this code runs at 100% failure but saves and executes, and I honestly don't know why. Could be I misunderstand the values I need to insert after the "function" area. I did have this setup with an "Auto Archive" trigger that created a menu button with a "Run" option on the sheet, but when you click that it only does the last row with "Yes" in column 28 (every press of the button will move 1 row until all rows are moved) and the button won't work for the other users of the sheet.

function createTrigger() {
  ScriptApp.newTrigger("Move Archive") //Move Archive is the name of the script
    .timeBased()
    .everyMinutes(1) // only set to 1 minute for testing, I can change this out for a daily timer
    .create();
}


function myFunction() {
  var sheetNameToWatch = "Schedule"; // "schedule" is the sheet we enter info on

  var columnNumberToWatch = 28; //Column is "AB"
  var valueToWatch = "Yes";
  var sheetNameToMoveTheRowTo = "Archive"; //"Archive is the sheet the info is sent to"

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange); // I get its programmed for last row with "Yes" here, unsure on how to change this.
    sheet.deleteRow(range.getRow());

    function myfunction() {
      ScriptApp.deleteTrigger("Move Archive"); // could have wrong value here

    }
  }
}

All I want is the sheet to "Archive" based on a "Yes" value in Column 28 (AB). I want every row with that column value to Archive at 1 am automatically. Any help is appreciated. If someone even wants to recommend a book or digital instruction for beginners that would be great.

Answer 1

You have four project files each containing scripts of the same name, performing the same or similar tasks. You are experiencing lagging because you have multiple simple and installable scripts of the same name. Essentially, they are all trying to execute at the same time.

The following answer should be considered as one possible solution to your situation.

The elements of this script are:

• There is a single project file. Unnecessary project files have been deleted.
• There is a single function.
• The function is a single "simple" trigger (onEdit(e)) which takes advantage of the various event objects returns by onEdit. There are no installable triggers, and any/all installable triggers have been deleted.
• The function updates the "Schedule" and "Archive" sheets as described in the question; and then sorts both the "Schedule" and "Archive" sheets.
• If there is a change on the "Railcars" sheet, the function sorts that sheet.

---

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var testrange = e.range;
  var testsheet = testrange.getSheet();
  var testsheetname = testsheet.getSheetName();
  var testrow = testrange.getRow();
  var testcolumn = testrange.getColumn();
  var testvalue = e.value;
  var testsheetLC = testsheet.getLastColumn();
  var testsheetRange = testsheet.getRange(testrow,1,1,testsheetLC);
  //Logger.log("DEBUG: the test sheet range is "+testsheetRange.getA1Notation()); 
  //Logger.log("DEBUG: Range: "+testrange.getA1Notation());
  //Logger.log("DEBUG: The row is "+testrow+", and the column is "+testcolumn);
  //Logger.log("DEBUG: The spreadheetsheet is "+e.source.getName()+", the sheet name is  "+testsheet+", the range = "+testrange.getA1Notation()+", and the new value = "+testvalue);
  //Logger.log(JSON.stringify(e));

  // Copy/Paste to Schedule/Archive
  var sheetNameSchedule = "Schedule";
  var colNumberSchedule = 28;
  var valueSchedule = "Yes";
  var sheetNameArchive = "Archive";

  // Sort Schedule
  var sortSchedule = [{column: 1, ascending: true},{column: 2, ascending: true},{column: 7, ascending: false}];// date // Appt (time) // Type (Out/In/RR)
  // Sort Railcars
  var sheetNameRailcars = "Railcars";
  var sortRailcars = [{column: 1, ascending: true}];

  if (testsheetname === sheetNameSchedule && testcolumn === colNumberSchedule && testvalue === valueSchedule){
    // this is a match
    // Logger.log("DEBUG: this was a match");

    // copy/paste to archive
    var targetSheet = ss.getSheetByName(sheetNameArchive);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    //Logger.log("DEBUG: the target range is "+targetRange.getA1Notation());
    testsheetRange.moveTo(targetRange);
    testsheet.deleteRow(testrow);

    // sort the Schedule Sheet
    var Avals = testsheet.getRange("A1:A").getValues();
    var Alast = Avals.filter(String).length;
    //Logger.log("DEBUG: The last row of content (in column A) = "+Alast+", and the last column = "+testsheetLC);
    var sortrange = testsheet.getRange(2,1,Alast-1,testsheetLC);
    //Logger.log("DEBUG: the sort range = "+sortrange.getA1Notation());
    sortrange.sort(sortSchedule);

    // sort the Archive Sheet
    var ATvals = targetSheet.getRange("A1:A").getValues();
    var ATlast = ATvals.filter(String).length;
    //Logger.log("DEBUG: The last row of content (in column A) = "+ATlast+", and the last column = "+testsheetLC);
    var sortrange = targetSheet.getRange(2,1,ATlast-1,testsheetLC);
    //Logger.log("DEBUG: the sort range = "+sortrange.getA1Notation());
    sortrange.sort(sortSchedule);
  }
  else if (testsheetname === sheetNameRailcars){

    // sort the sheet
    var Avals = testsheet.getRange("A1:A").getValues();
    var Alast = Avals.filter(String).length;
    //Logger.log("DEBUG: The last row of content (in column A) = "+Alast+", and the last column = "+testsheetLC);
    var sortrange = testsheet.getRange(2,1,Alast-1,testsheetLC);
    //Logger.log("DEBUG: the sort range = "+sortrange.getA1Notation());
    sortrange.sort(sortRailcars);
  } 

}

Answer 2

You can set up an installable trigger to run this function at 1am every morning:

function scheduleToArchive() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var schedule = sheet.getSheetByName("Schedule");
  var archive = sheet.getSheetByName("Archive");
  var scheduleData = schedule.getRange(2, 1, (sheet.getRange('A:A').getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()), 28);

  for (row = 2; row <= scheduleData.getNumRows(); row++){
    var data = schedule.getRange(row, 1, 1, 28).getValues();

    if (data[0][27] == 'Yes'){
     archive.getRange((archive.getLastRow() + 1), 1, 1, 28).setValues(data);
    }    
    schedule.getRange(row, 1, 1, 28).clear();
  }  
}

In your spreadsheet, when getDataRange() is run on the sheet named 'Schedule', it returns row 631 as the last row with data even though there is only data in the first 20 rows, so to get around that I've used SpreadsheetApp.Direction.DOWN instead and run that on column A.