CODEWITHSUNDEEP
google-apps-script
ThinkLife Pro
ThinkLife Pro

Reputation: 11

How do I use time based trigger to execute code when sheets is closed

I'm trying to execute the below code and I want it to be triggered at midnight instead of being executed when I change the 8th column. I'd like when I change the 8th column that it doesn't move the row until midnight. Any help would be greatly appreciated.

function TO_List() {
 // moves a row from a sheet to another when a magic value is entered in a column
 // adjust the following variables to fit your needs
 // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
var columnNumberToWatch = 8;
 // column A = 1, B = 2, etc…
 var valueToWatch = 'List';
 var sheetNameToMoveTheRowTo = 'List';
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveCell();

 if (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());
 }
}

Upvotes: 0

Views: 122

Answers (1)

Cooper
Cooper

Reputation: 64062

This looks like a container bound script so Spreadsheet.getActive() will work okay since you have to specify the spreadsheet in the trigger setup but getActiveSheet() will always return the same sheet as Spreadsheet.getSheets()[0] (the left most sheet) so you may have to use getSheetByName() if you want to run from a trigger.

I'm not sure but this might be close to what you want:

function active_to_List() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var tsh=ss.getSheetByName('List');
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(vA[i][7]=="List") {
      tsh.appendRow(vA[i]);
      tsh.deleteRow(i+1-d++);
    }
  }
}

Upvotes: 2

Related Questions