Background
Sometimes, even serverless architecture is too heavy... We don't want to bother with any cloud provider or setup a database. I find Google App Script as HTTP end point and Google Sheets as database a handy solution for ad hoc / peronal application.
I recently want to fully log how I use WeChat in order to retrospect and reduce time wasted on this App. Following implementation takes about 1 hour including writing this article.
Prerequisite
- A Gmail/ Gsuit account.
- Create A Google Sheets to receive the incoming data.
WebApp Code
Create a Google App Script with followig content: (replace RECORD_SHEET_ID with your actual value)
RECORD_SHEET_ID = 'XXXX';
TIMEZONE = 'GMT+8';
function appendToSheet(event, value1, value2) {
var ss = SpreadsheetApp.openById(RECORD_SHEET_ID);
var tab = ss.getSheetByName('Records');
var lastRow = tab.getLastRow();
var d = new Date();
var ts = Utilities.formatDate(new Date(), TIMEZONE, "yyyy-MM-dd HH:mm:ss");
var range = tab.getRange(lastRow + 1, 1, 1, 4);
range.setValues([ts, event, value1, value2](https:
}
function doGet(event) {
const { parameter } = event;
var eventName = parameter.event;
var value1 = parameter.value1;
var value2 = parameter.value2;
appendToSheet(eventName, value1, value2);
const output = `Received \\({eventName} with values: \\){value1}, ${value2}`;
return ContentService.createTextOutput(output);
};
function myFunction() {
appendToSheet('Test', 'v1')
}
Deployment
Authorize on the code tab
Execute myFunction from the Code tab. Upon first execution, it prompts you to allow this App Script to access Google Sheets content. Confirm. You should already see some content written to the Sheets, according to the codes in myFunction.
Deploy as webapp
Find the Deploy button and New Deployment.
Test deployment
The default URL end point for the WebApp is very long: (The DEPLOYMENT_ID part needs to be your own deployment ID)
https://script.google.com/macros/s/DEPLOYMENT_ID/exec
We append the query string parameters in the end:
https://script.google.com/macros/s/DEPLOYMENT_ID/exec?event=TestFromHTTP&value1=TestValue1
Your browser shall show the following HTTP response:
Received TestFromHTTP with values: TestValue1, undefined
(optional) Set URL redirection from own domain
This step is optional but I find it handy for two reasons:
- Get shorter URLs
- The deployment ID is different upon code change. This allows the caller of those WebApp to have a fix entry point. Each time upon code change, we can just update the URL redirection service.
I use CloudFlare to map a path from one of my own domain to the AppScript end point:
(optional) Test from client
For my original use case, we make an HTTP call from iOS ShortCut once WeChat is open.
Test result
The test result are as follows:
Limitations
Google Sheets is said to have 10 million cell limit. You may want to well plan the schema and lifecycle of the WebApp.
In my use case, even if I open WeChat 100 times per day, the solution can run for 100 years, which sounds good enough.
