Google Sheets selection script

code.gs

var trackingCell = 'O1';

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  
  if (sheet.getName() === 'People' && range.getColumn() === 2) {
    var selectedPeople = [];
    var peopleRange = sheet.getRange('A2:B'); // Adjust range as needed
    var values = peopleRange.getValues();

    for (var i = 0; i < values.length; i++) {
      if (values[i][0] === '') {
        break;
      }
      if (values[i][1]) { // If checkbox is checked
        selectedPeople.push(values[i][0]);
      }
    }

    // Get the cell where we'll write out the attendees
    var attendeesCell = sheet.getRange(trackingCell).getValue();

    var meetingsSheet = e.source.getSheetByName('Meetings');
    var activeCellMeetings = meetingsSheet.getRange(attendeesCell);
    var newPeople = selectedPeople.join(', ');
    activeCellMeetings.setValue(newPeople);
  }
}

function onSelectMembers() {
  // Clear log
  var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logs');
  logSheet.clear();

  // Get the Attendees column based off selected meeting title
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var activeColumn = activeCell.getColumn();
  if (activeColumn !== 1 || activeCell.getValue() === '' || activeCell.getValue() === 'Meeting Title') {
    var ui = SpreadsheetApp.getUi();
    ui.alert('A valid meeting title was not selected. \n Please select a meeting from Column A to add attendees.')
  } else {
    // Attendees cell is in column E (5)
    var attendeesCell = sheet.getRange(activeCell.getRow(), 5).getA1Notation();
    
    // Record the Attendees cell for onEdit
    var peopleSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People');
    peopleSheet.getRange(trackingCell).setValue(attendeesCell);
    
    // Check only the correct people
    var attendees = sheet.getRange(attendeesCell).getValue().split(', ');
    
    var namesRange = peopleSheet.getRange('A2:A').getValues();
    var checkboxesRange = peopleSheet.getRange('B2:B');
    var checkboxesValues = checkboxesRange.getValues();
    for (var i = 0; i < namesRange.length; i++) {
      if (namesRange[i][0] === '') {
        break;
      }
      if (attendees.includes(namesRange[i][0])) {
        checkboxesValues[i][0] = true; // Check the checkbox
      } else {
        checkboxesValues[i][0] = false;  
      }
    }
    checkboxesRange.setValues(checkboxesValues);

    // Open the People sheet for selection
    SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(peopleSheet);
  }
}

function onDone() {
  // Hide the People sheet
  var peopleSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People');
  peopleSheet.hideSheet();

  // Switch back to the Meetings sheet
  var meetingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Meetings');
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(meetingsSheet);

  
  var attendeesCell = peopleSheet.getRange(trackingCell).getValue();
  var meetingRow = meetingsSheet.getRange(attendeesCell).getRow();
  meetingsSheet.setActiveRange(meetingsSheet.getRange('A' + meetingRow));
}

function logMessage(message) {
  var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logs');
  if (!logSheet) {
    logSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('Logs');
  }
  logSheet.appendRow([new Date(), message]);
}