[Appscript] Google Sheet: Insert timestamp column when a new row is created/edited (and send notification to slack channel)

[Appscript] Google Sheet: Insert timestamp column when a new row is created/edited (and send notification to slack channel)

It has been long time I did not note any programing note, I nearly forget how to blog. As my work responsibility is changing from programer to digital marketing, things(doc and data) in marketing is not as clean as in software engineering, also, the mindset of marketers is also different to engineer, they spend much less time in thinking of automation and tracking of work. That is the reason why I am in the team.

And now Google Appscript is my best friend, since I discovered how powerful it is, which is a mix of semi-CMS and semi-database. More then enough to integrate with Datastudio, GA, search console and external application(ex Slack).

This is the script on gist, and the sample data sheet on google.

In this article, I am going to show a simple automation of:

1. A row was added in sheet-1
2. Add a column of timestamp called createdAt/updatedAt
3. Send a push notification to slack

Step-0 | Clone the spreadsheet

This is the sample Google sheet link, for repeat the automation please make a copy of it and do the programing.

Step-1 | Configuring the trigger and code

Configuring of the code and then configuring the trigger and executing.

After you save, try adding a new record on sheet Test , and you will see the console.log result on execution window.

Step-2 | while Updating Column A then adding createdAt and updatedAt on Column B and C

This is the most important lines of code of this example, this requires you to understand how we should think in spreadsheet system.

Look at line 18–27, we check if createdAt col is empty, if yes then inserting current date to both createdAt and updatedAt Columns, if it is not empty then inserting just to updatedAt Column.

(in app script, Col-A is 1,Col-B is 2 etc.)

Step-3 | call Slack API

The last piece of code is Slack api , check this out to see the way of using Slack Bot API.

UrlFetchApp.fetch(SLACK_WEBHOOK_LINK, {
  'method' : 'post',
  'contentType': 'application/json',
  'payload' : JSON.stringify({text: message})
});

Conclusion

This tiny script is very very helpful to me and my team, as we all active on Slack, so we can easily know what everyone is going on thought PC and mobile, even when we are off from our computer.

Reference:

— https://gist.github.com/wahengchang/71bb4090e3730e1673376fc86f728a12

— https://docs.google.com/spreadsheets/d/1nUz-G_igZry2Ge3sBqixF_wvlWWUupxwBNNRsu4tYYQ/edit#gid=0