使用Google Sheet Apps Script提升工作效率

const CURRENT = 'current'

function getTheNextStandupPerson() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const dataRange = activeSheet.getDataRange()
  const standupList = dataRange.getValues()

  const currentStandupPersonIndex = standupList.findIndex(person => person[1].toUpperCase() === CURRENT.toUpperCase())

  const tempStandupPersonIndex = currentStandupPersonIndex + 1
  const nextStandupPersonIndex =  tempStandupPersonIndex >= standupList.length ? 0: tempStandupPersonIndex
  const nextStandupPerson = standupList.find((_, index) => index === nextStandupPersonIndex )

  return {
    currentStandupPersonIndex,
    nextStandupPersonIndex,
    nextStandupPerson,
  }
}


function updateRecord(currentStandupPersonIndex, nextStandupPersonIndex, nextperson ) {
  Logger.log(`Start to update the latest standup person to ${nextperson}`)
  let currentRow = currentStandupPersonIndex +1
  let nextRow = nextStandupPersonIndex + 1

  const activeSheet = SpreadsheetApp.getActiveSheet()

  activeSheet.getRange(currentRow, 2).setValue('')
  activeSheet.getRange(nextRow, 2).setValue(CURRENT)

   Logger.log(`Successfully update the latest standup person to ${nextperson}`)
}

const WEBHOOK_URL = "https://chat.googleapis.com/v1/spaces/AAAAPhOvPUg/messages?key=AIzaSxxxxxxxxxxxxxxxxxxxxxxxx";

// main function
function sendMessageToChat() {
  if (!isMonday()) return;

  const {currentStandupPersonIndex,
    nextStandupPersonIndex,
    nextStandupPerson } = getTheNextStandupPerson()

  updateRecord(currentStandupPersonIndex, nextStandupPersonIndex, nextStandupPerson)

  const options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json; charset=UTF-8"
    },
    "payload": JSON.stringify({
      "text": `Hey <users/all>,This week's standup host is ${nextStandupPerson} ${'\n'.repeat(3)} The sheet: https://docs.google.com/spreadsheets/d/123434234343/edit#gid=0`
    })
  };
  const response = UrlFetchApp.fetch(WEBHOOK_URL, options);
  Logger.log(response);
}

function isMonday() {
  const date = new Date();
  const day = date.getDay();
  return ![0,2,3,4,5,6].includes(day)
}