1

I am trying to make this as simple as possible for users in our office that may not know how to edit script.. right now I have the following code (see below) however I have to list out each sheet name instead id like to include all sheets in this file automatically.

function onEdit(event) {
  dateStamp(event);
  moveClosedCases(event);
 
}
 
function dateStamp(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(event.source)
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  //CORE VARIABLES
  // The column you want to check if something is entered.
  var COLUMNTOCHECK = 11;
  // Where you want the date time stamp offset from the input location. [row, column]
  var DATETIMELOCATION = [0, -10];
  // Sheets you are working on
  var SHEETNAMES = ['JC', 'AU', 'AP', 'NA', 'BG', 'CE']
 
  //checks that we're on the correct sheet.
  if (SHEETNAMES.includes(s.getSheetName())) {
    //checks the column to ensure it is on the one we want to cause the date to appear.
    if (r.getColumn() === COLUMNTOCHECK) {
      var dateTimeCell = r.offset(DATETIMELOCATION[0], DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());
    }
  }
}
 
 
function moveClosedCases(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  // Sheets you are working on
  var SHEETNAMES = ['JC', 'AU', 'AP', 'NA', 'BG', 'CE']
  var COLUMNTOCHECK = 2 //1 = A, 2 = B etc.
 
  if (SHEETNAMES.includes(s.getName()) && r.getColumn() == COLUMNTOCHECK && (['Closed', 'closed', 'No Response', 'no response', 'No response'].includes(r.getValue()))) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Closed Cases");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}
 

1 Answer 1

0

You could use the getSheets method of Class Spreadsheet to get all the sheets of a spreadsheet.

Example

/**
 * Writes to the console the name of all the sheets of the active spreadsheet
 */
function logAllSheetsNames(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  sheets.forEach(sheet => console.log(sheet.getName());
}

Related

Resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.