Automating My Life with Google Apps Script: A Beginner’s Journey

As a newcomer to the world of coding, I recently decided to dip my toes into Google Apps Script. I’d heard it was a great way to automate tasks in Google Workspace, and boy, was that an understatement! Let me take you through my journey of learning and creating something new with Apps Script.

Getting Started

First things first, I needed to learn the basics. I started with Google’s own tutorials and documentation. The “Automation quickstart” was particularly helpful, showing me how to create a simple script that generates a Google Doc and emails me a link. It was like magic watching my code come to life!

My Bright Idea

After getting comfortable with the basics, I had a lightbulb moment. As someone who loves to read but often forgets to log the books I’ve finished, I thought, “What if I could automate my reading log?”

The Reading Log Automator

Here’s what I set out to create:

1. A Google Form where I could quickly input book details (title, author, date finished, rating).
2. A script that would automatically add this information to a spreadsheet.
3. A monthly email summary of all the books I’ve read.

The Code Adventure

Creating the form was easy enough, but the real fun began with the script. Here’s a simplified version of what I came up with:

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reading Log");
  var responses = e.response.getItemResponses();
  
  var rowData = [new Date()]; // Timestamp
  for (var i = 0; i < responses.length; i++) {
    rowData.push(responses[i].getResponse());
  }
  
  sheet.appendRow(rowData);
}
function sendMonthlySummary() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Reading Log");
  var data = sheet.getDataRange().getValues();
  
  var today = new Date();
  var lastMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  
  var booksRead = data.filter(function(row) {
    var date = new Date(row[0]);
    return date >= lastMonth && date < today;
  });
  
  var emailBody = "Books read last month:\n\n";
  booksRead.forEach(function(book) {
    emailBody += book[1] + " by " + book[2] + " - Rating: " + book[4] + "/5\n";
  });
  
  MailApp.sendEmail({
    to: "myemail@example.com",
    subject: "Monthly Reading Summary",
    body: emailBody
  });
}

The Results

After some trial and error (and a lot of Googling), I got it working! Now, every time I submit the form, the data automatically populates my spreadsheet. Plus, on the first of each month, I get a neat email summarizing all the books I read in the previous month.

How I setup my Project:-

1. Create a Google Form:

• Go to Google Forms and create a new form
• Add fields for Book Title, Author, Date Finished, and Rating

2. Create a Google Sheet:

• Go to Google Sheets and create a new spreadsheet
• Rename the first sheet to “Reading Log”

3. Link the Form to the Sheet:

• In your Google Form, click on “Responses”
• Click on the Google Sheets icon
• Select “Create a new spreadsheet” or link to your existing “Reading Log” sheet

4. Open the Script Editor:

• In your Google Sheet, go to “Extension” > “Apps Script”

5. Paste the Code:

• In the Script editor, paste the provided code

6. Modify the email address:

• In the sendMonthlySummary function, replace "myemail@example.com" with your actual email address

7. Save the project:

• Click on “File” > “Save” and give your project a name

8. Set up triggers:

• In the Script editor, click on the clock icon (Triggers)
• Click “Add Trigger”
• For onFormSubmit:
• Choose the function to run: onFormSubmit
• Select event source: From spreadsheet
• Select event type: On form submit
• For sendMonthlySummary:
• Choose the function to run: sendMonthlySummary
• Select event source: Time-driven
• Select type of time based trigger: Month timer
• Select time of month: 1st of the month
• Select time of day: Midnight to 1am

9. Authorize the script:

• The first time you run the script, you’ll need to authorize it
• Click “Review Permissions” and then “Allow”

10. Test the script:

• Submit a form response and check if it appears in your “Reading Log” sheet
• To test the monthly summary, you can temporarily change the trigger to run daily or hourly, or manually run the sendMonthlySummary function from the Script editor

Lessons Learned

This project taught me so much:

1. The power of automation: What used to take me several manual steps now happens instantly.
2. The joy of problem-solving: Figuring out how to filter data by date was particularly satisfying.
3. The importance of documentation: Google’s Apps Script documentation was a lifesaver.

What’s Next?

I’m already thinking about how to expand this project. Maybe I could add a visualization of my reading habits over time, or integrate with the Goodreads API for more book details. Learning Google Apps Script has opened up a world of possibilities for automating my digital life. It’s amazing what you can accomplish with just a bit of code and a lot of imagination. If you’re on the fence about trying Apps Script, I say go for it! Who knows what you might create?

So, whether you’re a tech enthusiast, a professional, or just someone who wants to learn more, I invite you to follow me on this journey. Subscribe to my blog and follow me on social media to stay in the loop and never miss a post.

Together, let’s explore the exciting world of technology and all it offers. I can’t wait to connect with you!”

Connect me on Social Media: https://linktr.ee/mdshamsfiroz

Happy coding! Happy learning!