Notify EPS drivers

notifyDriver.gs

function locale(x) { return Utilities.formatDate(new Date(x), 'PST', 'EEE MMM d, yyyy') }

function constants() {
  return {
    spreadsheetId: '1RTKJTvzfst_ZVQHUyEy2PnB4P075verlDJLKMgb0UFU',
    sheetId: 16057777,
    startRow: 2,
    startCol: { letter: 'A', num: 1 },
    endCol: { letter: 'E', num: 5 }
  }
}

function getSheetById(spreadsheet, sheetId) {
  return spreadsheet.getSheets().filter(sheet => sheet.getSheetId() === sheetId)[0]
}

function getSheet() {
  const ss = constants()
  const { spreadsheetId } = ss
  const { sheetId } = ss
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId)
  const sheet = getSheetById(spreadsheet, sheetId)
  return sheet
}

function email(recipent, content, subject) {
  return GmailApp.sendEmail(
    recipent, 
    `Reminder: your ${subject} will expire soon`,
    '',
    {
      htmlBody: content,
      name: 'BEES Admin',
      cc: 'beesadmin@ucr.edu'
    }
  )
}

// trigger weekly on Tuesday
function driverExpired() {
  function days(x) {
    const daysInMilliSeconds = 86400000
    return x * daysInMilliSeconds
  }
  const ss = constants()
  const { startRow } = ss
  const { letter: startColLetter } = ss.startCol
  const { letter: endColLetter } = ss.endCol
  const sheet = getSheet()
  const endRow = sheet.getRange('!A1:A').getValues().filter(String).length+1
  const rangeString = `${startColLetter}${startRow}:${endColLetter}${endRow}`
  const range = sheet.getRange(rangeString)
  const data = range.getValues()
  
  // users sheet
  const usersSheet = getSheetById(SpreadsheetApp.openById(ss.spreadsheetId), 972991645)
  const userseEndRow = usersSheet.getRange('!A1:A').getValues().filter(String).length+1
  const usersRangeString = `A1:B${userseEndRow}`
  const usersRange = usersSheet.getRange(usersRangeString)
  const usersData = usersRange.getValues()
  
  for (const i in data) {
    const currentRowNum = (Number(i)+startRow).toFixed(0)
    const name = data[i][0]
    const license = data[i][1]
    const insurance = data[i][2]
    const licenseDate = license != '' ? new Date(license).valueOf() : ''
    const insuranceDate = insurance != '' ? new Date(insurance).valueOf() : ''
    const today = new Date().valueOf()
    const notify = data[i][4]
    const licenseExpiresWithinOneMonth = licenseDate && (licenseDate<(today+days(30)))
    const insuranceExpiresWithinOneMonth = insuranceDate && (insuranceDate<(today+days(30)))
    
    if (!notify && (!licenseExpiresWithinOneMonth && !insuranceExpiresWithinOneMonth)) {
      sheet.getRange(`${endColLetter}${currentRowNum}`).setValue('TRUE') // enable notifications
    }
    
    if (notify && (licenseExpiresWithinOneMonth || insuranceExpiresWithinOneMonth)) {      
      sheet.getRange(`${endColLetter}${currentRowNum}`).setValue('FALSE') // disable notifications
      
      const subject = licenseExpiresWithinOneMonth && !insuranceExpiresWithinOneMonth ? 'driver\'s license' : 
        insuranceExpiresWithinOneMonth && !licenseExpiresWithinOneMonth ? 'auto insurance' : 
        licenseExpiresWithinOneMonth && insuranceExpiresWithinOneMonth ? 'driver\'s license & auto insurance': 
        'driver authorization'
      const content = 
`Hi there, your ${subject} will expire soon. To renew your Earth & Planetary Science Driver Authorization, please show your renewed ${subject} at 2460A Geology.
<br>
<br><strong>Name</strong>: ${name}
<br><strong>License Expires</strong>: ${locale(licenseDate)}
${new Date(insurance).valueOf() != new Date('1/1/2100').valueOf() ? `<br><strong>Auto Insurance Expires</strong>: ${locale(insuranceDate)}`: ''}
<br>
<br>-BEES Admin Auto Emailer`
      
      const userEmail = usersData.filter(row => row[0] == name )[0][1] // filter matching name and get email
      
      // notify
      email(userEmail, content, subject) 
    }
  }
  
      
  return console.log('success') // notify total notifies
}