Is there any solution to prevent me from having to manually renew the authorization repeatedly?

[楊昀晟]

Hi everyone, I'm new to here, and i got some trouble with deploy my google script web app.

I created an web app, use LINE BOT & Google Script to control Google sheet.

Here is my architecture. Picture1

I set Permissions:

• Excute as: Excute the app as me
• Who has access: Anyone

When i deployed this web app in first time, and the access window show up, like Picture2.

And i click allow, and all thing is work!

But about five days later, my GAS is not work, into Execution page, it's show doPost function did not receive message event from Line Bot.

And i click the Deploy button again, the Access Window like Picture2 show up again.

so i think is the authorization has expired. i need to renew it regularly.

Is there any solution to prevent me from having to manually renew the authorization repeatedly?

Thank you for taking the time to read my questions. If there’s any solution, I’d really appreciate it if you could let me know.

[Andrew Roberts]

If the Line API uses OAuth2 and this is you having to manually refresh the access token, try the OAuth2 library from Google which will do that for you.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/3fbef09e-b8a4-42bf-8b23-4dba8cfd0130n%40googlegroups.com.

[楊昀晟]

Thanks for getting back to me!

I have two more questions:

1. Does the OAuth2 library need to be implemented with a Google Service Account?

2. If so, does that mean I need to modify the code that operates Google Sheets (e.g., CacheService, LockService, SpreadsheetApp...) and instead use the Google Sheets API?

3. Here is a simplified version of my current script:  

const LINE_CHANNEL_ACCESS_TOKEN = PropertiesService.getScriptProperties().getProperty('LINE_CHANNEL_ACCESS_TOKEN')

const cache = CacheService.getScriptCache() // default expired time: 600s(10min), max: 21600s(6hr)

const lock = LockService.getScriptLock()

const currentDate = new Date()

const currentYear = currentDate.getFullYear() // 2025

const currentMonth = currentDate.getMonth() + 1 // 0 means January

const currentDay = currentDate.getDate() // 1

function doPost(e) {

  try {

    let eventObj = JSON.parse(e.postData.contents)

    let eventType = eventObj.events[0].type

    let timestamp = eventObj.events[0].timestamp

    let eventId = eventObj.events[0].message.id

    let userMessage

    if (eventType !== 'follow' && eventType !== 'message') {

      return

    }

    if (eventType === 'message') {

      userMessage = eventObj.events[0].message.text

      if (!isCommand(userMessage)) {

        return

      }

    }

    // 防止處理相同的上傳要求兩次

    if (cache.get(eventId)) {

      return

    }

    cache.put(eventId, 'proccessed', 60)

    let replyToken = eventObj.events[0].replyToken

    if (!replyToken) {

      console.log("No valid replyToken found.");

      throw new Error('replyToken is no valid!')

    }

    // let sourceType = eventObj.events[0].source.type

    let userID = eventObj.events[0].source.userId

    // let groupID = ''

    // if (sourceType === 'group') {

    //   groupID = eventObj.events[0].source.groupId

    // }

    let userName = cache.get(userID)

    if (!userName) {

      let userProfile = getUserProfile(replyToken, userID)

      cache.put(userID, userProfile.displayName, 21600)

      userName = userProfile.displayName

    }

    let sheet = SpreadsheetApp.openByUrl(spreadsheetUrl)

    if (eventType === 'follow') {

      setUserData(replyToken, userName, userID, sheet)

    } else if (eventType === 'message') {

      if (userMessage === '/設定基本資料') {

        setUserData(replyToken, userName, userID, sheet)

      } else if (userMessage === '/提醒推播測試') {

        pushToUser(userID, '測試成功!')

      } else if (userMessage === '/查詢提醒時間') {

        let userSheet = sheet.getSheetByName('LINE_UserInfo')

        let userData = userSheet.getDataRange().getValues()

        checkRemindTime(replyToken, userID, userName, userData)

      } else if (userMessage.startsWith('/設定提醒時間')) {

        let timeString = userMessage.slice(7).trim()

        if (!/^\d{1,2}$/.test(timeString)) {

          replyToUser(replyToken, `「/設定提醒時間」後面只能接0~23的數字!`)

          return

        }

        let remindTime = parseInt(timeString, 10)

        if (isNaN(remindTime) || remindTime < 0 || remindTime > 23) {

          replyToUser(replyToken, `「/設定提醒時間」後面只能接0~23的數字!`)

          return

        }

        let userSheet = sheet.getSheetByName('LINE_UserInfo')

        let userData = userSheet.getDataRange().getValues()

        updateRemindTime(replyToken, remindTime, userID, userName, userSheet, userData)

      } else {

        let workSheet = sheet.getSheetByName('美術部')

        let lastRow = workSheet.getLastRow()

        let lastColumn = workSheet.getLastColumn()

        let startRow = getStartRow(replyToken, workSheet)

        let dateData = workSheet.getRange(startRow, 1, lastRow, 2).getValues()

        let usersCellSet = workSheet.getRange(1, 1, 1, lastColumn).getValues()

        let todayRow = getTodayRow(dateData, startRow)

        let userColumn = findUserColumn(userID, usersCellSet, lastColumn)

        if (!todayRow || !userColumn) {

          replyToUser(replyToken, '找不到todayRow或userColumn!')

          return

        }

        if (userMessage === '/查詢今日上傳內容') {

          checkTodayProgress(replyToken, todayRow, userColumn, userName, workSheet)

        } else if (userMessage.includes('/上傳進度')) {

          let cleanText = userMessage.replace(/@\S+\s*/g, "").trim()

          let progressText = cleanText.split('/上傳進度')[1].trim()

          updateProgress(replyToken, todayRow, userColumn, userName, progressText, workSheet)

        }

      }

    }

    // 紀錄log到event_log

    let eventLog = sheet.getSheetByName('event_log')

    let logTime = new Date(timestamp)

    lock.waitLock(30000)

    eventLog.appendRow([userID, userName, eventId, logTime])

    SpreadsheetApp.flush() // 將所有待處理的變更提交至試算表。

    lock.releaseLock()

  } catch (error) {

    console.log('doPost failed!', error.message)

  } finally {

    lock.releaseLock()

  }

}

function isCommand(userMessage = '') {

  const commands = ['/設定基本資料', '/提醒推播測試', '/查詢今日上傳內容', '/查詢提醒時間']

  let result = false

  if (commands.includes(userMessage)) {

    result = true

  } else if (userMessage.includes('/上傳進度')) {

    result = true

  } else if (userMessage.startsWith('/設定提醒時間')) {

    result = true

  }

  return result

}

Andrew Roberts 在 2025年4月1日 星期二下午1:56:53 [UTC+8] 的信中寫道：

[Andrew Roberts]

1. Does the OAuth2 library need to be implemented with a Google Service Account?

No. Just include the ID in the Script editor

1. If so, does that mean I need to modify the code that operates Google Sheets (e.g., CacheService, LockService, SpreadsheetApp...) and instead use the Google Sheets AP

No. You can continue to use the built-in service.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/6c3077a0-1113-4fb9-8e5b-c4a8cc89c487n%40googlegroups.com.

[George Ghanem]

It does not ask you to re-authorize that fast usually unless you made some software changes and are using a new service that you did not authorize initially.

Usually inactive scripts will get removed from authorization after a few months (I have had that happen to me on some less frequently used triggers).

--

[楊昀晟]

I'm not sure about the reason since the only authorization required is for Google Sheet.

In my case, I am the owner of the GAS, but I only have Editor access to the Google Sheet.

But that's okay! I think I'll give the OAuth2 library a try and see if it helps solve my current issue. 

Huge thanks to everyone for your help! 

Also, if there's anything I can improve in the way I ask questions, feel free to let me know. I really appreciate it!

George Ghanem 在 2025年4月2日 星期三凌晨1:26:54 [UTC+8] 的信中寫道：