Skip to main content

How I used Google Sheets and Apps Script

Google Sheet is one of the most powerful spreadsheet application that exists online, rivaling with Microsoft's Excel. One of the main strengths is its strong support for collaboration with other users, much easier and popular than collaboration tools with Microsoft Office.

Aside from plain spreadsheet, it also supports extensions such as macro. If you are familiar with macros on other office tools, they work almost the same. However, the most extension I use and tinker with is the Apps Scipt.

Apps Script Extension

One of the challenges I faced recently is how do I track or monitor reports in our department if they are submitted on time or worst, forgotten due to lack of better monitoring tools. So I thought if there can be simple applications that can be deployed or use by a more general user to allow reminding periodically what reports are approaching due dates or those that are past dues.

Then I looked for a way, instead of creating a full blown app from scratch, what if I can use tools such as Google Sheets. Then I found out about the Apps Script extension where it lets you code to have some automation capabilities on the spreadsheet. One more thing is that it is based on javascript, one of the most popular languages nowadays for building web applications.

Another thing I looked for is that if I can send emails with Apps Script. It turns out we can using the MailApp.sendMail function! This is great. One last thing though that needs an answer. Can I have automated tasks in Apps Script where I can run specific function periodically. And voila, Apps Script has triggers!

Turns out that a trigger on Apps Script can be of 3 different sources:

  • From spreadsheet
  • Time-driven
  • From calendar

The solution I chose was the Time-driven. This is where we can schedule repetitive tasks such as hourly or daily etc. and what I thought is enough for my requireent. I would just make a daily schedule of checking if there are reports that needs attention at specific time everyday.

Set Up

There are two places where I should work on. First is the spreadsheet where I will put the tasks table for listing tasks or reports and emails where the notifications will be sent, and then on Apps Script extension where I put the functions for parsing the tasks and send emails periodically.

Spreadsheet

For the spreadsheet, I wanted to create a baseline data as minimal as possible. So to think about it, first I need the date for the due date, and then I need to have a data if a specific report or task is already submitted or not, and then lastly is the name of the report. This should be in a reports table with its own worksheet.

For the list of emails to be notified, I put it in a separate worksheet with its own table.

Apps Script

Now that we have the spreadsheet out of the way, here comes some coding part. Basically, for the most simplified tasks, I need these base functions:

  • Get the list of email
  • Get all tasks that are not yet checked for submission and get their due dates
  • Send a formatted email notification to each of the emails listed on the spreadsheet

After the functions are set, I now proceeded with the trigger. As discussed earlier, the source I selected for the trigger is time-driven as this is what I can think of is appropriate for this project. I then set the trigger to run the function everyday between 8 and 9 am. So far it works for me helping me with the deadlines that I could miss easily!

Conclusion

I think this can be a helpful tool for anybody wanting the same functionality and doesn't require a full blown application. As of now, it is in testing phase for me but is already being helpful.

I hope you get value from this content! Thank for reading and see you on the next one!

Labels:

Comments

Post a Comment

Popular posts from this blog

Automate Sending Email with Apps Script and Google Sheet

Introduction It has been too long that many people uses Microsoft Excel in day-to-day computing tasks. It's so big that it almost resemble a programming language where non-technical people can create their own spreadsheet programs. It has many uses with just the default grid-type data entries. But Microsoft Office developers did not stopped there. They gave it more power by adding a scripting capability to it with VBA or Visual Basic for Applications. Most of the office apps of Microsoft has this VBA at their disposal but I most used it with Microsoft Excel. It was the most appropriate application for me to use it. But then come the big competition. I'll skip the open source apps that may compete with Microsoft Office and go directly with the big one. This is the Google Sheet from Google. Introducing Google Sheet Google Sheets is an online spreadsheet application that allows users to create, edit, and format spreadsheets to organize and analyze information....
Post a Comment
Read more

From Checkers to Consciousness: Tracing the Roots of AI

 Artificial intelligence (AI) and machine learning (ML) have exploded into our lives, powering everything from our music playlists to groundbreaking medical diagnoses. Even how our mobile phone cameras take pictures uses AI. But where did this extraordinary revolution begin? And how did we go from clunky, rule-based systems to the sophisticated, data-driven intelligence we see today? The quest to build intelligent machines has captivated thinkers, scientists, mathematicians and many more for centuries, with pioneers like Alan Turing laying crucial theoretical groundwork long before the formal establishment of the field.  Now let's dive into the fascinating origin story and explore the ever-expanding contributions of these transformative technologies. The Seeds of Intelligence: Early Days of AI The concept of artificial intelligence has roots in ancient myths and fictional automatons. However, the formal pursuit of AI as a scientific field began in the mid-20th century. Alan Tu...
Post a Comment
Read more