Excel

便利ですよね。もう最初に出会ってから何十年も経ちますが、いまだに資料を作るときに「Excelでこうすれば。。。」のように考える自分がいます。

もし自分が起業して、時間給のスタッフを抱えるようになった時に、
「勤務表をExcelで作ると。。。」みたいなことを考えるのではないかなと思いました。

今回やりたいこと

Excelではなく、Googleスプレッドシートを使用して、ユーザーが出勤・退勤を申告した時に、
Googleスプレッドシートと、LINEWORKSの勤怠管理用のカレンダーにそれぞれの時間を登録する。

という作業をBotに行ってもらおうと思います。

前提として、1回の勤務は同日中に終わる事を想定としています。
なので、「前日のPM10時に出勤・翌日AM8時に退勤」のような勤務体系には適用できません。m(_ _)m

イメージ図は以下の通りです。

で、GASのスクリプトの構想はこんな感じで考えました。

準備①：LINEWORKSの勤怠登録用カレンダーを作成する

以下のように「勤怠登録用カレンダー」というカレンダーを作成して、必要なメンバーにカレンダーを共有しました。

カレンダーの共有方法はこちらをご確認ください。

準備②：Googleスプレッドシートの準備

まず「format」というシートを作成し、すごーくシンプルですが以下のような形式にしました。

単に日付と、出勤時間・退勤時間、あとは「退勤時間 - 出勤時間」で勤務時間を割り出しているだけです。

Google App Scriptのエディターを表示し、過去の参考の記事と同様にライブラリの追加やLINEWORKS側のBotの準備などを整えます。

ではBotの作成に進みましょう。

Bot作成

まずは全体像

以下のようにスクリプトを記述しました。ながっ！

testcode.js

function doPost(e) {
  if (e == null || e.postData == null || e.postData.contents == null) return
  let requestJSON = e.postData.contents
  let requestObj = JSON.parse(requestJSON)
  let env = getEnv_()
  env.userId = requestObj.source.userId 
  let contentText = requestObj.content.text
  let calendarId = "勤怠登録用カレンダーのカレンダーID" 
  let issuedTime = new Date(requestObj.issuedTime) 

  if (contentText == "出勤"||contentText == "退勤"){

//Token取得
  let token = LINEWORKS.getAccessToken(env, "user,calendar").access_token

//名前を取得
  let fullName = getName(env,token)
  let text = `こんにちは${fullName}さん。`
  LINEWORKS.userMessageSend(env, text)

//勤怠用カレンダーの予定リストを確認して重複チェック
  let errchk = chk(env,token,calendarId,issuedTime,fullName,contentText)
  if (errchk != null){
    text = `${errchk}`
    LINEWORKS.userMessageSend(env, text)
    return
  }

//ユーザーに出勤時間を伝えるメッセージを送る
  let msgTime = Utilities.formatDate(issuedTime,"JST","yyyy年MM月dd日HH:mm")
  text = `${contentText}時間は${msgTime}です。`
  LINEWORKS.userMessageSend(env, text)

//指定カレンダーに予定を登録
  entCal(env,token,calendarId,fullName,issuedTime,contentText)

//Googleスプレッドシートに時間を登録する
  entSheet(fullName,issuedTime,contentText)

  text = "以上です"
  LINEWORKS.userMessageSend(env, text)
  }else{
  text = "「出勤」か「退勤」を選択してください"
  LINEWORKS.userMessageSend(env, text)
  }
}

function getName(env,token){
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId 
  let options = { 
    "headers": {
        "Authorization": "Bearer " + token
    },
    "method": "get"
  }  
  let response = UrlFetchApp.fetch(uri,options) 
  let lwUser = JSON.parse(response)
  let userLastName = lwUser.userName.lastName
  let userFirstName = lwUser.userName.firstName
  return userLastName + userFirstName
}

function chk(env,token,calendarId,issuedTime,fullName,contentText){
  let today = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd")
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events?fromDateTime=" + today + "T00:00:00%2B09:00&untilDateTime=" + today + "T23:59:59%2B09:00"
  let options = { 
    "headers": {
        "Authorization": "Bearer " + token
    },
    "method": "get"
  }
  let response = UrlFetchApp.fetch(uri,options)
  let parsed = JSON.parse(response)
  let count = parsed.events.length
  for (let i = 0; i < count ; i++){ 
    if (parsed.events[i].eventComponents[0].summary == `${contentText}：${fullName}`){
      let errMsg 
      return errMsg = `今日はすでに${contentText}ボタンを押しています。カレンダー画面を確認してください　https://line.worksmobile.com/calendar?version=26`
    }
  }
}

function entCal(env,token,calendarId,fullName,issuedTime,contentText){
  let calTime = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd'T'HH:mm:ss")
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events"
  let json = {
    "eventComponents": [
      {
        "summary": `${contentText}：${fullName}`,
        "start": {
          "dateTime": calTime,
          "timeZone": "Asia/Tokyo"
        }
      }
    ]
  }
  let options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + token
    },
    "payload": JSON.stringify(json)
  }
  UrlFetchApp.fetch(uri, options)
}

function entSheet(fullName,issuedTime,contentText){
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getSheetByName(fullName)
  let sheetTime = Utilities.formatDate(issuedTime,"JST","HH:mm") 
  let dt = Utilities.formatDate(issuedTime,"JST","dd") 
  let entRow = Number(dt)
  entRow = entRow + 1
  let entCol
  if (contentText == "出勤"){
    entCol = 2
  }else{
    entCol = 3
  }
  if(sheet == null){  //同名のシートが無かった場合はformatシートをコピーして新しいシートを作成する
    let source = ss.getSheetByName("format") //コピー元のシートを特定
    let newSheet = source.copyTo(ss) //新しいシートを作成
    let addsheet = newSheet.setName(fullName) //新しいシートの名前を設定
    addsheet.getRange(entRow,entCol).setValue(sheetTime)
      }else{
    sheet.activate()
    sheet.getRange(entRow,entCol).setValue(sheetTime)
    }  
}

//JWTの生成（これはgetAccessTokenの流れで実行されるfunction)
function getJwt(ENV){
  const header = Utilities.base64Encode(JSON.stringify({"alg":"RS256","typ":"JWT"}), Utilities.Charset.UTF_8)
  const claimSet = JSON.stringify({
    "iss": ENV.CLIENT_ID,
    "sub": ENV.SERVICE_ACCOUNT,
    "iat": Math.floor(Date.now() / 1000),
    "exp": Math.floor(Date.now() / 1000 + 2000)
  })
  const encodeText = header + "." + Utilities.base64Encode(claimSet, Utilities.Charset.UTF_8)
  const signature = Utilities.computeRsaSha256Signature(encodeText, ENV.PRIVATE_KEY)
  return encodeText + "." + Utilities.base64Encode(signature)
}

//AccessTokenの取得（assertionでgetJwtが実行されている)
function getAccessToken(ENV, scopes) {
  const uri = "https://auth.worksmobile.com/oauth2/v2.0/token"
  const payload = {
    "assertion" : getJwt(ENV),
    "grant_type" : encodeURIComponent("urn:ietf:params:oauth:grant-type:jwt-bearer"),
    "client_id": ENV.CLIENT_ID,
    "client_secret": ENV.CLIENT_SECRET,
    "scope": scopes
  }
  const options = {
    "method": "post",
    "headers": {"Content-Type" : "application/x-www-form-urlencoded"},
    "payload": payload
  }
  return JSON.parse(UrlFetchApp.fetch(uri, options))
}

function getEnv_() {
  return {
    CLIENT_ID: "Client_id",
    CLIENT_SECRET: "secret",
    SERVICE_ACCOUNT: "service_accout@domain",
    PRIVATE_KEY: "-----BEGIN PRIVATE KEY-----
                   -----END PRIVATE KEY-----",
    DOMAIN_ID: domainId,
    ADMIN_ID: "adminID@Domain",
    BOT_ID: BotNo
  }
}

function monthlySpreadSheet(){
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getActiveSheet()
  let ssid = SpreadsheetApp.getActiveSpreadsheet().getId()
  let sheetCount = ss.getNumSheets()
  var fSheet = ['format']
  let dt = new Date()
  let year = dt.getFullYear()
  let month = dt.getMonth()

//新しいシートを作成する シート名を「yyyymm」（mmは前月）とする

  let newSheet = SpreadsheetApp.create(`${year}${month}`).getId()
  let dist = SpreadsheetApp.openById(newSheet)
  let sourceSSid = SpreadsheetApp.openById(ssid)

//formatシート以外を新しいシートにコピーする
  for(let i = sheetCount; i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]
    for(var k = 0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      let sourceName = sh.getName()
      let sourceSheet = sourceSSid.getSheetByName(sourceName)
      sourceSheet.copyTo(dist)
    }
  }

//コピーしたformat以外のシートを削除
  for(let i = sheetCount;i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]

    for(var k =0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      ss.deleteSheet(sh)
    }
  }
}

最初の変数定義

testcode.js

function doPost(e) {
  if (e == null || e.postData == null || e.postData.contents == null) return
  let requestJSON = e.postData.contents
  let requestObj = JSON.parse(requestJSON) 
  let env = getEnv_()
  env.userId = requestObj.source.userId
  let contentText = requestObj.content.text
  let calendarId = "勤怠登録用カレンダーのカレンダーID" 
  let issuedTime = new Date(requestObj.issuedTime) 

  if (contentText == "出勤"||contentText == "退勤"){

この部分は以前作成した予定通知Botでも説明した部分になるので、大半は割愛しますが、ちょっとだけ説明します。

  let contentText = requestObj.content.text

callbackで受信するデータの中で、メッセージの本文となる「content.text」を変数に代入します。
ここで「出勤」や「退勤」を申告することによって、カレンダーに登録される件名が変わったりします。

  let calendarId = "勤怠登録用カレンダーのカレンダーID"

勤怠登録用カレンダーのカレンダーIDを変数に代入しておきます。
事前にユーザーのカレンダーリストの取得APIなどで、カレンダーIDを確認しておきましょう。

  let issuedTime = new Date(requestObj.issuedTime)

今回は色々なところで時間を使用します。

callbackで受信するデータの「issuedTime」というメッセージが作成された時間があります。
stringのデータなので、Date型へ変換して変数へ代入します。

  if (contentText == "出勤"||contentText == "退勤"){

contentText（ユーザーからのメッセージ）が「出勤」or「退勤」の場合は以下の処理を行う。という条件式です。
リッチメニューで「出勤・退勤」を選択してもらう予定ですが、それ以外のメッセージが手入力で送られた場合は、エラーメッセージを返すようになってます。

リッチメニューの登録方法は以前のBot作成で触れていますので割愛。

ユーザーの名前を取得する

Googleスプレッドシートのシート名や、LINEWORKSカレンダーの予定登録時にユーザーの名前を利用します。
今回は処理ごとにfunctionを分けるという記述方法も学びました！

ユーザーの名前を取得する部分は以下です。

doPost

//Token取得
  let token = LINEWORKS.getAccessToken(env, "user,calendar").access_token

//名前を取得
  let fullName = getName(env,token)
  let text = `こんにちは${fullName}さん。`
  LINEWORKS.userMessageSend(env, text)

getName

function getName(env,token){
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId
  let options = { 
    "headers": {
        "Authorization": "Bearer " + token
    },
    "method": "get"
  }  
  let response = UrlFetchApp.fetch(uri,options) 
  let lwUser = JSON.parse(response) 
  let userLastName = lwUser.userName.lastName
  let userFirstName = lwUser.userName.firstName
  return userLastName + userFirstName
}

ユーザー名の取得は以前作成したBotでも説明したので詳細は割愛します。

functionの分け方を自分の備忘録として。。。

doPost

  let fullName = getName(env,token)

フルネームを取得するために、getNameファンクションを実行します。
getNameファンクションではユーザー取得APIが実行されますが、APIを実行するためには、環境の情報(env)と認証情報(token)が必要なので、それぞれを引数としてgetNameへ渡します。

getName

function getName(env,token){

getName側では、最初に(env,token)と記載されています。
この記述は以下の処理が行われているとイメージします。

「getNameファンクション内で使用する変数envを宣言」して、「doPostの変数envを代入」する
「getNameファンクション内で使用する変数tokenを宣言」して、「doPostの変数tokenを代入」する

↑これが今までよくイメージできてませんでした...(;_;)

  return userLastName + userFirstName

そして引数として渡された情報を基にAPIを実行して、取得した結果をdoPostファンクション側に返します(return)。

同日中に重複して出勤・退勤を申告していないか？

同じ日に何度も出勤・退勤することが無いようにカレンダーをチェックします。
もし既に勤怠が登録されていた場合はメッセージを送って終了します。

doPost

//勤怠用カレンダーの予定リストを確認して重複チェック
  let errchk = chk(env,token,calendarId,issuedTime,fullName,contentText)
  if (errchk != null){ 
    text = `${errchk}`
    LINEWORKS.userMessageSend(env, text)
    return
  }

chk

function chk(env,token,calendarId,issuedTime,fullName,contentText){
  let today = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd")
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events?fromDateTime=" + today + "T00:00:00%2B09:00&untilDateTime=" + today + "T23:59:59%2B09:00"
  let options = { 
    "headers": {
        "Authorization": "Bearer " + token
    },
    "method": "get"
  }
  let response = UrlFetchApp.fetch(uri,options)
  let parsed = JSON.parse(response)
  let count = parsed.events.length
  for (let i = 0; i < count ; i++){
    if (parsed.events[i].eventComponents[0].summary == `${contentText}：${fullName}`){
      let errMsg 
      return errMsg = `今日はすでに${contentText}ボタンを押しています。カレンダー画面を確認してください　https://line.worksmobile.com/calendar?version=26`
    }
  }
}

指定カレンダーの予定リストの取得APIを使用して、「出勤(or退勤）:ユーザーのフルネーム」の予定が既に登録されていないかをチェックします。

doPost

  let errchk = chk(env,token,calendarId,issuedTime,fullName,contentText)

指定カレンダーの予定リストの取得APIでは、環境情報(env)や認証情報(token)の他に以下の情報が必要になりますので、chkファンクションへ引数を渡します。

引数	用途
calenderId	勤怠登録用カレンダーを指定するため
issuedTime	APIのクエリパラメータで「yyyy-mm-dd」の日付けが必要です
fullName	予定の件名に含まれるユーザー名で重複を確認するため
contentText	「出勤」or「退勤」を見分けるため

chk

function chk(env,token,calendarId,issuedTime,fullName,contentText){
  let today = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd")
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events?fromDateTime=" + today + "T00:00:00%2B09:00&untilDateTime=" + today + "T23:59:59%2B09:00"
  let options = { 
    "headers": {
        "Authorization": "Bearer " + token
    },
    "method": "get"
  }

APIを実行します。

chk

  let today = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd")

API実行時の**クエリパラメータとして、「fromDateTime」(いつから)と「untilDateTime」(いつまで)**を何時何分何秒というところまで指定しなければいけません。

当日中をチェックするので、時間は「00:00:00~23:59:59」で良いと思いますが、日付は毎日変わります。
なので、変数todayにissuedTimeで取得した年月日を代入します。

chk

  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events?fromDateTime=" + today + "T00:00:00%2B09:00&untilDateTime=" + today + "T23:59:59%2B09:00"

このように、userIdとcalenderIdとtodayを使用して開始日時・終了日時を設定してAPIを実行します。

chk

  let response = UrlFetchApp.fetch(uri,options)
  let parsed = JSON.parse(response)
  let count = parsed.events.length
  for (let i = 0; i < count ; i++){ 
    if (parsed.events[i].eventComponents[0].summary == `${contentText}：${fullName}`){
      let errMsg 
      return errMsg = `今日はすでに${contentText}ボタンを押しています。カレンダー画面を確認してください　https://line.worksmobile.com/calendar?version=26`
    }
  }
}

登録されている予定を取得した後、予定の件数を確認して条件判定を繰り返します。

chk

    if (parsed.events[i].eventComponents[0].summary == `${contentText}：${fullName}`){
      let errMsg 
      return errMsg = `今日はすでに${contentText}ボタンを押しています。カレンダー画面を確認してください　https://line.worksmobile.com/calendar?version=26`
    }

予定登録時の件名は「出勤（退勤）：ユーザーのフルネーム」となりますが、同一の件名が無いかをチェックします。
もし同一の件名があった場合、エラーメッセージをdoPostファンクションにreturnします。

doPost

  if (errchk != null){
    text = `${errchk}`
    LINEWORKS.userMessageSend(env, text)
    return

doPostファンクション側では、chkファンクションからreturnされたerrchkの内容がnullだった場合、特に何もしませんが、
もしerrchkがnullではない場合（エラーメッセージが含まれている場合）は、エラーメッセージを送信して、処理を終了します。

ユーザーに出勤（退勤）時刻を伝える

この部分は基本的にメッセージ送信しているだけです。
callbackのissuedTimeをそのまま送信するとちょっと見づらいので、見やすい形式に変換している程度です。

doPost

//ユーザーに出勤時間を伝えるメッセージを送る
  let msgTime = Utilities.formatDate(issuedTime,"JST","yyyy年MM月dd日HH:mm") 
  text = `${contentText}時間は${msgTime}です。`
  LINEWORKS.userMessageSend(env, text)

LINEWORKSのカレンダーに予定を登録する

指定カレンダーの予定の登録APIを実行して、勤怠登録用カレンダーに出勤（退勤）を登録します。

doPost

//指定カレンダーに予定を登録
  entCal(env,token,calendarId,fullName,issuedTime,contentText)

entCalファンクションではAPIを実行するために、先ほどの予定リストの確認と同様の引数が必要となります。

entCal

function entCal(env,token,calendarId,fullName,issuedTime,contentText){
  let calTime = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd'T'HH:mm:ss")
  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events"
  let json = {
    "eventComponents": [
      {
        "summary": `${contentText}：${fullName}`,
        "start": {
          "dateTime": calTime,
          "timeZone": "Asia/Tokyo"
        }
      }
    ]
  }
  let options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + token
    },
    "payload": JSON.stringify(json)
  }
  UrlFetchApp.fetch(uri, options)
}

カレンダーに予定を登録するために必要な時間の表示形式が、先ほどの予定リストの確認と違うんですよね。。。

entCal

function entCal(env,token,calendarId,fullName,issuedTime,contentText){
  let calTime = Utilities.formatDate(issuedTime,"JST","yyyy-MM-dd'T'HH:mm:ss")

なので、calTimeとして予定を登録するための時間を成形して代入します。

entCal

  let uri = "https://www.worksapis.com/v1.0/users/" + env.userId + "/calendars/" + calendarId + "/events"
  let json = {
    "eventComponents": [
      {
        "summary": `${contentText}：${fullName}`,
        "start": {
          "dateTime": calTime,
          "timeZone": "Asia/Tokyo"
        }
      }
    ]
  }

「summary」が予定の件名です。件名は「出勤：山田太郎」「退勤：山田花子」のように登録されます。
時間は「calTime」を設定します。

entCal

  let options = {
    "method": "POST",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + token
    },
    "payload": JSON.stringify(json)
  }
  UrlFetchApp.fetch(uri, options)
}

これをPOSTで実行します。
特にdoPostファンクションにreturnする情報が無いので、「 UrlFetchApp.fetch(uri, options)」を実行して終わっています。

Googleスプレッドシートに入力

次にGoogleスプレッドシートに時間を入力します。
Googleスプレッドシートの操作はWebで検索すると色々ありますけど、私のように知識がない者には正直分かりづらい説明ばかりで、今回Googleスプレッドシートの操作が一番苦戦しました(-_-;;

doPost

//Googleスプレッドシートに時間を登録する
  entSheet(fullName,issuedTime,contentText)

Googleスプレッドシート側での入力には以下の情報が必要なので、引数として渡します。

引数	用途
fullName	シート名はユーザーのフルネーム
issuedTime	出勤・退勤時刻を入力するため
contentText	出勤か退勤かによって、入力列を変更します

entSheet

function entSheet(fullName,issuedTime,contentText){
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getSheetByName(fullName)
  let sheetTime = Utilities.formatDate(issuedTime,"JST","HH:mm") 
  let dt = Utilities.formatDate(issuedTime,"JST","dd") 
  let entRow = Number(dt)
  entRow = entRow + 1
  let entCol
  if (contentText == "出勤"){
    entCol = 2
  }else{
    entCol = 3
  }
  if(sheet == null){  //同名のシートが無かった場合はformatシートをコピーして新しいシートを作成する
    let source = ss.getSheetByName("format") //コピー元のシートを特定
    let newSheet = source.copyTo(ss) //新しいシートを作成
    let addsheet = newSheet.setName(fullName) //新しいシートの名前を設定
    addsheet.getRange(entRow,entCol).setValue(sheetTime)
      }else{
    sheet.activate()
    sheet.getRange(entRow,entCol).setValue(sheetTime)
    }  
}

ちょっと長いので、まずやりたい事を図にするとこのような感じです。

entSheet

function entSheet(fullName,issuedTime,contentText){
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getSheetByName(fullName)

スプレッドシート内にユーザーのフルネームが付与されたシート名があるかどうかをチェックします。
もし無ければ、変数sheetにnullが入ります。

entSheet

  let sheetTime = Utilities.formatDate(issuedTime,"JST","HH:mm") 
  let dt = Utilities.formatDate(issuedTime,"JST","dd")

シートに入力する時間は「何時何分」だけで良いので、issuedTimeを成形します。

entSheet

  let dt = Utilities.formatDate(issuedTime,"JST","dd") 
  let entRow = Number(dt)
  entRow = entRow + 1

何行目のセルに入力するかを判定するために、issuedTimeの日付け部分を利用します。
スプレッドシートの1行目は項目名が記載されている行なので、+1行目を入力行とします。

entSheet

  let entCol
  if (contentText == "出勤"){
    entCol = 2
  }else{
    entCol = 3
  }

入力する列は出勤か退勤かによって変わります。
出勤の場合は2列目・退勤の場合は3列目が入力列となります。

entSheet

  if(sheet == null){
    let source = ss.getSheetByName("format") //コピー元のシートを特定
    let newSheet = source.copyTo(ss) //新しいシートを作成
    let addsheet = newSheet.setName(fullName) //新しいシートの名前を設定
    addsheet.getRange(entRow,entCol).setValue(sheetTime)
      }else{
    sheet.activate()
    sheet.getRange(entRow,entCol).setValue(sheetTime)
    }

変数sheetがnullだった（ユーザー名のシートが無い）場合、「format」シートをコピーして、ユーザー名のシートを作成して、時刻を入力します。
変数sheetがnullではない（ユーザー名のシートが存在する）場合は、該当のシートの入力箇所に時刻を入力します。

動作確認

Botの動きは以下のようになりました。

二回目の出勤・退勤でエラーが表示されるようになってます！

Googleスプレッドシート側は以下のように、ユーザー名のシートが作成され、
上のgifで表示された日付の行に時間が入力されました！

...という感じでBot作成が完了しました。が！！

当初想定していた通り、BotがLINEWORKSカレンダーとGoogleスプレッドシートに時間を入力することができ、Googleスプレッドシートではユーザーのシートが無かった場合、新規作成する。という動作もできるようになりました。

ですが、作り終わってから
「月が変わったらどうするの？」
ということに気づきました。。。

なので、月が替わった場合は以下のように処理しようと考えました。

それが以下の部分です。

monthlySpreadSheet

function monthlySpreadSheet(){
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let sheet = ss.getActiveSheet()
  let ssid = SpreadsheetApp.getActiveSpreadsheet().getId()
  let sheetCount = ss.getNumSheets()
  var fSheet = ['format']
  let dt = new Date()
  let year = dt.getFullYear()
  let month = dt.getMonth()

//新しいシートを作成する シート名を「yyyymm」（mmは前月）とする

  let newSheet = SpreadsheetApp.create(`${year}${month}`).getId()
  let dist = SpreadsheetApp.openById(newSheet)
  let sourceSSid = SpreadsheetApp.openById(ssid)

//formatシート以外を新しいシートにコピーする
  for(let i = sheetCount; i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]
    for(var k = 0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      let sourceName = sh.getName()
      let sourceSheet = sourceSSid.getSheetByName(sourceName)
      sourceSheet.copyTo(dist)
    }
  }

//コピーしたformat以外のシートを削除
  for(let i = sheetCount;i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]

    for(var k =0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      ss.deleteSheet(sh)
    }
  }
}

新しいスプレッドシートを作成する

これは日付を起点として実行される処理なので、別functionとして作成しました。

まずは新しいスプレッドシートを作成します。

monthlySpreadSheet

//新しいシートを作成する シート名を「yyyymm」（mmは前月）とする

  let newSheet = SpreadsheetApp.create(`${year}${month}`).getId()

Dateで取得した年・月をスプレッドシートの名前にして、新しいスプレッドシートを作成しています。
Dateで取得した月はデフォルトで前月になっているので、そのまま使用します。

後で新しいスプレッドシートのスプレッドシートIDが必要になるので、getIdでスプレッドシートIDを取得しておいて、変数newSheetに代入しておきます。

formatシート以外をコピーする

ここはマジでどうやったら良いんだろう。。。と悩んだのですが、このWebに記載されている内容がとても分かりやすかったです！

なのでここを参考に処理をコピーに変更してみました！
説明は上記のリンクを見てくださいw

monthlySpreadSheet

//formatシート以外を新しいシートにコピーする
  for(let i = sheetCount; i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]
    for(var k = 0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      let sourceName = sh.getName()
      let sourceSheet = sourceSSid.getSheetByName(sourceName)
      sourceSheet.copyTo(dist)
    }
  }

formatシート以外を削除する

このWebのまんまです！！

monthlySpreadSheet

//コピーしたformat以外のシートを削除
  for(let i = sheetCount;i > 1 ; i--){
    var flag = true
    var sh = ss.getSheets()[i-1]

    for(var k =0; k < fSheet ; k++){
      if(sh.getName() == fSheet[k]){
        flag = false
        break
      }
    }
    if(flag == true){
      ss.deleteSheet(sh)
    }
  }

トリガーを設定する

毎月1日の夜間にmonthlySpreadSheetファンクションを実行して新しいスプレッドシートを作成するため、トリガーを設定しました。

Google App Script画面の左側に時計のアイコンがあるのでこちらをクリックします。

こんな感じでトリガーを設定しました。
バージョンの数がメチャクチャ大きいのは苦戦した証と思ってください(^^;)

まとめ

今回使用したLINEWORKSのAPIは以下です。

今回は正直LINEWORKSのAPIよりもスプレッドシートの操作や、GASの書き方が勉強になりました~

※本当はAPIのresponse headerからHTTPステータスコードをreturnして、もし何らかの問題でAPIの処理ができなかった場合、doPost側でエラーを通知する。のようなことが出来ればより良いかなと思っています。
後日余力があったら追記します。。。

LINEWORKSと勤怠関連を連携するなら。。。

今回は「もし勤怠表をExcelで作ったら。。。」という世界線をベースにスプレッドシートに置き換えてBotを作成しました。

が、LINEWORKSでは「らくしふ」というサービスと連動できます。

去年のアドベントカレンダーでLINEWORKSと「らくしふ」の連動について記事が書かれております！

「らくしふ」はシフト管理の専門的なサービスなので、当然今回作成したBotよりも色々なことができるはずなので、Excelシフト表からの脱却を図るチャンスかもしれませんね~