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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

xiaoming1
xiaoming2
xiaoming3
xiaoming4
xiaoming5
xiaoming6	current
xiaoming7
xiaoming8
xiaoming9
xiaoming10

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

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}`)
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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)
}