Pradyumna.me

Automation Made Simple with Google Apps Script

Pradyumna Krishna
Pradyumna Krishna

Ever spent hours copying data between spreadsheets, sending repetitive emails, or manually pulling information from websites? I've been there. My quest for an accessible automation solution led me to Google Apps Script—a surprisingly powerful tool hidden in plain sight within the Google ecosystem.

In this article, I'll show you why Google Apps Script might be the workflow automation secret weapon you've been missing. I've implemented dozens of automations with it that have saved my clients countless hours and thousands of dollars in operational costs.

What makes Google Apps Script special?

Google Apps Script is a JavaScript-based platform that seamlessly integrates with Google Workspace applications. What sets it apart?

  • Zero infrastructure management: It runs on Google's cloud, eliminating the need for servers or local setup
  • Native Google integration: Direct access to Docs, Sheets, Gmail, Calendar, and other Google services
  • Flexible execution: Run scripts on schedule, via triggers, webhooks, or on-demand
  • Low barrier to entry: Approachable for users with basic programming knowledge
  • Free to use: Available at no cost (with reasonable quotas and limitations)

For someone who needs automation throughout the workday but doesn't want to manage servers or complex systems, it's the perfect middle ground between no-code tools and custom development.

Real-world example: Daily weather report automation

Let's build something practical: an automated script that fetches weather data from an API and emails you a daily report. Here's the code:

function fetchAPI() {
  // In a real implementation, uncomment to fetch actual data
  // const url = 'https://weather-api.onpy.in/';
  // const response = UrlFetchApp.fetch(url);
  // return JSON.parse(response.getContentText());

  // Sample data for demonstration
  return [
    {
      timestamp: '2023-07-12T09:30:00Z',
      city: 'New York',
      temperature: 78.6,
      conditions: 'Partly Cloudy',
      humidity: 65,
      windSpeed: 12,
    },
  ];
}

function sendMail(data) {
  let emailBody = 'Here is your daily weather report:\n\n';

  data.forEach((item) => {
    emailBody += `Time: ${new Date(item.timestamp).toLocaleString()}\n`;
    emailBody += `City: ${item.city}\n`;
    emailBody += `Temperature: ${item.temperature}°F\n`;
    emailBody += `Conditions: ${item.conditions}\n`;
    emailBody += `Humidity: ${item.humidity}%\n`;
    emailBody += `Wind Speed: ${item.windSpeed} mph\n\n`;
  });

  // Send the email
  const subject = 'Daily Weather Report';
  MailApp.sendEmail({
    to: 'your.email@example.com',
    subject: subject,
    body: emailBody,
  });
  Logger.log('Email sent successfully');
}

function main() {
  const data = fetchAPI();
  if (data.length === 0) {
    Logger.log('No data received from API');
    return;
  }

  sendMail(data);
}

Notice how simple it is to use services like MailApp and UrlFetchApp? Google has made these services available as global objects within the script environment, allowing for clean and direct integration.

Google Apps Script becomes even more powerful when you enable Advanced Google Services. With just a few clicks, you can integrate powerful APIs like Google Analytics, BigQuery, or Google Maps directly into your scripts.

Adding Advanced Google Services

Expanding functionality: Logging data to Google Sheets

Want to maintain historical records? Here's how to log that weather data to a spreadsheet with just a few lines of code:

function logDataToSpreadsheet(data) {
  const spreadsheetId = 'YOUR_SPREADSHEET_ID';
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);

  let sheet = spreadsheet.getSheetByName('Daily Weather');

  if (!sheet) {
    sheet = spreadsheet.insertSheet('Daily Weather');
    sheet
      .getRange(1, 1, 1, 6)
      .setValues([
        ['Date', 'City', 'Temperature (°F)', 'Conditions', 'Humidity (%)', 'Wind Speed (mph)'],
      ]);
  }

  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(lastRow + 1, 1, data.length, 6);

  const values = data.map((item) => [
    new Date(item.timestamp),
    item.city,
    item.temperature,
    item.conditions,
    item.humidity,
    item.windSpeed,
  ]);

  range.setValues(values);

  Logger.log('Data logged successfully to spreadsheet.');
}

This approach becomes particularly valuable when you need to visualize trends or share information with team members. I've used similar scripts to track everything from sales to stock prices.

Beyond Google: Integrating with external services

Need to connect with tools outside the Google ecosystem? No problem. Here's how to send alerts to Slack:

function sendSlackAlert(message) {
  const slackWebhookUrl = 'YOUR_SLACK_WEBHOOK_URL';

  const payload = {
    text: message,
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
  };

  UrlFetchApp.fetch(slackWebhookUrl, options);
  Logger.log('Slack alert sent');
}

This pattern works for any service with an API—Trello, Asana, GitHub, you name it.

Scheduling and deployment made easy

Setting up your script to run automatically is straightforward:

  1. Time-based triggers: Schedule scripts to run hourly, daily, weekly, or at specific times.

  2. Event-based triggers: Run scripts when someone edits a spreadsheet, submits a form, or opens a document.

  3. Web app deployment: Create API endpoints or simple web interfaces for your scripts.

Conclusion

I hope this article has sparked your interest in Google Apps Script. Give it a try and see how it can transform your workflow. The possibilities are endless, and the time you save will be well worth the investment.

Love to hear your thoughts; give me feedback on my mail or reach out to me on X. If you have any questions, feel free to ask!