Optimization assistance for script to create events with colors

Question

I have been learning a bunch and I am at a point where this script does all that it needs to do, but I'm not sure if it is optimal. This script creates calendar events with color customization.

In one sheet (help_sheet), I have the calendar_Id along with dropdowns to determine different colors for events that will be created. In another sheet (data_sheet), I have the data structured as:

column A Checkboxes

column B Type_Color

column C Title

column D Start Date

column E End Date

column H EventID

What are some ways to optimize? I have a feeling that the call to different colors is not optimized, but I have yet to learn how to do it. Is this where arrays and objects come in?

Thanks, folks!

function CreateSelectionToCalendar() {
  var sheetHelp = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('help_sheet');
  var CalendarID = sheetHelp.getRange(1,1).getValue();
  var cal = CalendarApp.getCalendarById(CalendarID);

  var sheetData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data_sheet');
  var rows = sheetData.getDataRange().getValues();
  var headers = rows.shift();
  
  rows.forEach(function(row, i) { 
    if(row[0]) {
      Logger.log(JSON.stringify(row));
      var type = row[1]
      var title = row[2];
      var tstart = row[3];
      var tstop = row[4];
      var eventID = row[7];

      var fillPaleBlue = sheetHelp.getRange(2,1).getValue();
      var fillPaleGreen = sheetHelp.getRange(3,1).getValue();
      var fillMauve = sheetHelp.getRange(4,1).getValue();
      var fillPaleRed = sheetHelp.getRange(5,1).getValue();
      var fillYellow = sheetHelp.getRange(6,1).getValue();
      var fillOrange = sheetHelp.getRange(7,1).getValue();
      var fillCyan = sheetHelp.getRange(8,1).getValue();
      var fillGray = sheetHelp.getRange(9,1).getValue();
      var fillBlue = sheetHelp.getRange(10,1).getValue();
      var fillGreen = sheetHelp.getRange(11,1).getValue();
      var fillRed = sheetHelp.getRange(12,1).getValue();

      var newEvent = cal.createEvent(title, tstart, tstop,);

      if (type === fillPaleBlue) {
        newEvent.setColor('1');
      } else if (type === fillPaleGreen) {
        newEvent.setColor('2');
      } else if (type === fillMauve) {
        newEvent.setColor('3');
      } else if (type === fillPaleRed) {
        newEvent.setColor('4');
      } else if (type === fillYellow) {
        newEvent.setColor('5');
      } else if (type === fillOrange) {
        newEvent.setColor('6');
      } else if (type === fillCyan) {
        newEvent.setColor('7');
      } else if (type === fillGray) {
        newEvent.setColor('8');
      } else if (type === fillBlue) {
        newEvent.setColor('9');
      } else if (type === fillGreen) {
        newEvent.setColor('10');
      } else if (type === fillRed) {
        newEvent.setColor('11');
      }
    }
  });
}

Answer 1

Try it this way:

function CreateSelectionToCalendar() {
  const ss = SpreadsheetApp.getActive();
  const hsh = ss.getSheetByName('help_sheet');
  const CalendarID = hsh.getRange(1, 1).getValue();
  const cal = CalendarApp.getCalendarById(CalendarID);
  const dsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data_sheet');
  const rows = dsh.getDataRange().getValues();
  const headers = rows.shift();
  rows.forEach(function (row, i) {
    if (row[0]) {
      let [, type, title, tstart, tstop, , , eventID] = row
      let [[fillPaleBlue], [fillPaleGreen], [fillMauve], [fillPaleRed], [fillYellow], [fillOrange], [fillCyan], [fillGray], [fillBlue], [fillGreen], [fillRed]] = hsh.getRange("A2:A12").getValues()
      let arr = [[fillPaleBlue], [fillPaleGreen], [fillMauve], [fillPaleRed], [fillYellow], [fillOrange], [fillCyan], [fillGray], [fillBlue], [fillGreen], [fillRed]].flat();
      let newEvent = cal.createEvent(title, tstart, tstop);
      newEvent.setColor(arr.indexOf(type) + 1);
    }
  });
}