From d63ab3912c45ea3aff53ce26eb141d6a090a94d3 Mon Sep 17 00:00:00 2001 From: ttsukagoshi Date: Thu, 5 Aug 2021 15:36:55 +0900 Subject: [PATCH 1/4] Add main branch to CodeQL workflow target on pull request --- .github/workflows/codeql-analysis.yml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/.github/workflows/codeql-analysis.yml b/.github/workflows/codeql-analysis.yml index 09270b6..9deabac 100644 --- a/.github/workflows/codeql-analysis.yml +++ b/.github/workflows/codeql-analysis.yml @@ -16,7 +16,7 @@ on: branches: [main, release] pull_request: # The branches below must be a subset of the branches above - branches: [release] + branches: [main, release] # schedule: # - cron: '20 18 * * 4' From 018493379e189bb21ebaa8740cf74eaa15b7ad8f Mon Sep 17 00:00:00 2001 From: Taro TSUKAGOSHI Date: Fri, 6 Aug 2021 06:57:44 +0900 Subject: [PATCH 2/4] Encourage users to refer to the website on how to use this solution --- README.md | 20 +------------------- 1 file changed, 1 insertion(+), 19 deletions(-) diff --git a/README.md b/README.md index 8c9f8d7..c0bc295 100644 --- a/README.md +++ b/README.md @@ -14,25 +14,7 @@ Copy [this sample spreadsheet](https://docs.google.com/spreadsheets/d/1JvO090Vcg ## How to Use -### Setup - -#### `01_Dashboard` Worksheet - -Replace the `WEBSITE NAME` and `TARGET URL` columns with those of the website(s) that you want to monitor. - -#### `90_Spreadsheets` Worksheet - -Delete everything **except the first row**. - -#### `99_Options` Worksheet - -Go over the parameters that you can set for this status monitoring and edit the `VALUE` items to suit you needs. - -#### Set Triggers - -From the spreadsheet menu, select `Web Status` > `Triggers` > `Set Status Check Trigger`/`Set Log Extraction Trigger` to set up time-based triggers to conduct automated status checks. The latest results will be shown in the `01_Dashboard` worksheet. - -You will be asked to authorized the script the first time you execute it. Users of free Gmail account should expect to see the `Unverified` warning during this authorization process. Note that the owner of the script is yourself, and that this solution will not send or receive any information to and from any other Google accounts or services outside the Google ecosystem (except for checking the HTTP response codes of the websites that you designated because, well, that's what it does for status monitoring) unless you explicitly share the spreadsheet. +See [website](https://www.scriptable-assets.page/gas-solutions/website-monitoring-by-gas/) for details on how to use this solution. ## Updates From 733ed7a82e96c49f54665ce8053945d6462444e6 Mon Sep 17 00:00:00 2001 From: ttsukagoshi Date: Fri, 6 Aug 2021 07:11:48 +0900 Subject: [PATCH 3/4] Add message to refer to website for latest info --- src/i18n.js | 2 ++ src/websiteMonitoring.js | 25 +++++++++++++++++++++---- 2 files changed, 23 insertions(+), 4 deletions(-) diff --git a/src/i18n.js b/src/i18n.js index 2e932eb..39b25e6 100644 --- a/src/i18n.js +++ b/src/i18n.js @@ -12,6 +12,8 @@ // See the License for the specific language governing permissions and // limitations under the License. +// See https://www.scriptable-assets.page/gas-solutions/website-monitoring-by-gas/ for latest updates. + /* exported LocalizedMessage */ const MESSAGES = { diff --git a/src/websiteMonitoring.js b/src/websiteMonitoring.js index ebb7fc0..92dbdd2 100644 --- a/src/websiteMonitoring.js +++ b/src/websiteMonitoring.js @@ -12,6 +12,8 @@ // See the License for the specific language governing permissions and // limitations under the License. +// See https://www.scriptable-assets.page/gas-solutions/website-monitoring-by-gas/ for latest updates. + /* global LocalizedMessage */ /* exported deleteTimeBasedTriggers, @@ -538,11 +540,26 @@ function websiteMonitoring(triggered = false) { 0, 'NA', ]); - MailApp.sendEmail( - myEmail, - localMessage.messageList.mailSubErrorStatusCheck, - localMessage.replaceMailBodyErrorStatusCheck(e.stack, ss.getUrl()) + let messageSub = localMessage.messageList.mailSubErrorStatusCheck; + let messageBody = localMessage.replaceMailBodyErrorStatusCheck( + e.stack, + ss.getUrl() ); + if (options.ENABLE_CHAT_NOTIFICATION) { + // Post on Google Chat + postToChat_( + options.CHAT_WEBHOOK_URL, + `*${messageSub}*\n\n${messageBody}` + ); + } + if ( + !options.ENABLE_CHAT_NOTIFICATION || + !options.DISABLE_MAIL_NOTIFICATION + ) { + // If chat notification is disabled OR mail notification is NOT disabled + // send email notification + MailApp.sendEmail(myEmail, messageSub, messageBody); + } if (!triggered) { ui.alert( localMessage.messageList.alertTitleError, From 85819a77ac7c9d9a0a9dd251ced6e29189617d70 Mon Sep 17 00:00:00 2001 From: ttsukagoshi Date: Sat, 7 Aug 2021 23:39:44 +0900 Subject: [PATCH 4/4] Close #34: record latest statuses on an independent worksheet, separate from the dashboard worksheet --- src/websiteMonitoring.js | 37 ++++++++++++++++++------------------- 1 file changed, 18 insertions(+), 19 deletions(-) diff --git a/src/websiteMonitoring.js b/src/websiteMonitoring.js index 92dbdd2..bb32082 100644 --- a/src/websiteMonitoring.js +++ b/src/websiteMonitoring.js @@ -26,15 +26,15 @@ websiteMonitoringTriggered // Sheet Names const SHEET_NAME_DASHBOARD = '01_Dashboard'; +const SHEET_NAME_LATEST_STATUS = '80_Latest Status'; +const SHEET_NAME_STATUS_LOGS_EXTRACTED = '81_Status Logs Extracted'; const SHEET_NAME_SPREADSHEETS = '90_Spreadsheets'; -const SHEET_NAME_STATUS_LOGS_EXTRACTED = '91_Status Logs Extracted'; const SHEET_NAME_OPTIONS = '99_Options'; // Header Names const HEADER_NAME_TARGET_URL = 'TARGET URL'; // Range parameters of the list of target websites in SHEET_NAME_DASHBOARD. const TARGET_WEBSITES_RANGE_POSITION = { row: 5, col: 2 }; // Position of the upper- and left-most cell including the header row const TARGET_WEBSITES_COL_NUM = 2; // Number of fields names (columns) in the list of target websites -const DASHBOARD_STATUS_COL_NUM = 3; // Number of fields names (columns) in the dashboard status ranges, adjacent to the list of target websites // Keys in SHEET_NAME_OPTIONS whose value should be converted to arrays const OPTIONS_CONVERT_TO_ARRAY_KEYS = [ 'ALLOWED_RESPONSE_CODES', @@ -415,35 +415,34 @@ function websiteMonitoring(triggered = false) { responseRecord.status = 'DOWN'; changes.newErrors.push(responseRecord); } - // Log result to the log spreadsheet - logSheet.appendRow([ - responseRecord.timeStamp, + // Latest status data for updating the log spreadsheet and the worksheet on latest statuses + let latestStatus = [ responseRecord.websiteName, responseRecord.targetUrl, responseRecord.responseCode, responseRecord.responseTime, responseRecord.status, - ]); - // Updates to the dashboard worksheet - dashboardStatus.push([ - responseRecord.status, - responseRecord.responseCode, responseRecord.timeStamp, - ]); + ]; + logSheet.appendRow(latestStatus); + dashboardStatus.push(latestStatus); // Update savedStatusUpdated savedStatusUpdated[responseRecord.targetUrlEncoded] = responseRecord; return changes; }, { newErrors: [], resolved: [] } ); - // Update the dashboard status - targetWebsitesSheet - .getRange( - TARGET_WEBSITES_RANGE_POSITION.row + 1, - TARGET_WEBSITES_RANGE_POSITION.col + TARGET_WEBSITES_COL_NUM, - dashboardStatus.length, - DASHBOARD_STATUS_COL_NUM - ) + // Update the worksheet on latest statuses + let latestStatusSheet = ss.getSheetByName(SHEET_NAME_LATEST_STATUS); + let existingStatusHeader = latestStatusSheet + .getDataRange() + .getValues() + .shift(); + latestStatusSheet.getDataRange().clearContent(); + dashboardStatus = [existingStatusHeader].concat(dashboardStatus); + console.log(dashboardStatus); + latestStatusSheet + .getRange(1, 1, dashboardStatus.length, dashboardStatus[0].length) .setValues(dashboardStatus); // Save the updated savedStatusUpdated in the document properties dp.setProperty(DP_KEY_SAVED_STATUS, JSON.stringify(savedStatusUpdated));