Intermediate Google Apps Script Templates | Ready-to-Use Automation

Intermediate Google Apps Script Templates

Ready-to-use templates for data processing, reports, and integrations

🚀 What You'll Build

These intermediate templates take you beyond basic automation to create powerful, production-ready solutions.

Skill Level: Intermediate

You should be comfortable with basic Apps Script concepts before using these templates. Each includes:

Complete, ready-to-use code
Setup instructions
Customization options
Error handling
Best practices

Prerequisites

Basic understanding of JavaScript
Familiarity with Google Sheets
Experience with Apps Script basics
Google Workspace account

📋 Template Quick Start

1 Choose Your Template

Select from data processing, reporting, or integration templates based on your needs.

2 Copy the Code

Copy the complete script into your Apps Script editor.

3 Configure Settings

Update variables like sheet names, email addresses, and API endpoints.

4 Set Up Triggers

Configure time-based or event-based triggers for automation.

💡 Pro Tip

Test each template with sample data before deploying to production. All templates include error handling for safe operation.

📊 Data Processing

📈 Automated Reports

🔄 API Integrations

⚡ Workflow Automation

Advanced Data Processing Templates

Transform, clean, and analyze data with these powerful processing scripts.

🔄

Data Validation & Cleaning

✓

Smart Data Validation

Automatically validate email formats, phone numbers, and custom patterns

🧹

Data Cleaning

Remove duplicates, standardize formats, and fix common data issues

// Data validation example
function validateAndCleanData() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const cleanedData = data.map(row => {
// Validate email format
if (row[1] && !isValidEmail(row[1])) {
row[5] = "INVALID_EMAIL";
}
// Standardize phone numbers
if (row[2]) {
row[2] = standardizePhone(row[2]);
}
return row;
});
sheet.getRange(1, 1, cleanedData.length, cleanedData[0].length)
.setValues(cleanedData);
}

📤

Multi-Sheet Data Sync

🔄

Cross-Sheet Synchronization

Keep multiple sheets synchronized with change tracking

⚡

Incremental Updates

Only process changed rows for better performance

// Multi-sheet sync example
function syncSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceSheet = ss.getSheetByName("Master");
const targetSheet = ss.getSheetByName("Archive");
// Get only new rows since last sync
const lastSync = PropertiesService.getScriptProperties()
.getProperty('lastSync') || 0;
const newData = getNewRowsSince(sourceSheet, lastSync);
if (newData.length > 0) {
targetSheet.getRange(targetSheet.getLastRow() + 1, 1,
newData.length, newData[0].length)
.setValues(newData);
// Update last sync timestamp
PropertiesService.getScriptProperties()
.setProperty('lastSync', new Date().getTime());
}
}

Automated Reporting Templates

Generate and distribute professional reports automatically.

📧

Email Dashboard Reports

📊

HTML Email Reports

Create beautiful, formatted email reports with charts and summaries

⏰

Scheduled Delivery

Automatically send reports daily, weekly, or monthly

// Email dashboard example
function sendDailyReport() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
// Generate report content
const summary = generateSummary(data);
const htmlContent = createHtmlReport(summary);
// Send to multiple recipients
const recipients = ["team@company.com", "manager@company.com"];
recipients.forEach(email => {
MailApp.sendEmail({
to: email,
subject: `Daily Report - ${new Date().toLocaleDateString()}`,
htmlBody: htmlContent
});
});
}

📈

PDF Export & Archive

📄

PDF Generation

Convert sheets and charts to professional PDF documents

💾

Automatic Archiving

Save reports to Google Drive with organized folder structure

// PDF export example
function exportToPDF() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Report");
// Create PDF blob
const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?` +
`format=pdf&size=A4&portrait=true&gridlines=false`;
const params = {
method: "GET",
headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
muteHttpExceptions: true
};
const blob = UrlFetchApp.fetch(url, params).getBlob();
blob.setName(`Report_${new Date().toISOString().split('T')[0]}.pdf`);
// Save to Drive
const folder = DriveApp.getFolderById("YOUR_FOLDER_ID");
folder.createFile(blob);
}

API Integration Templates

Connect Google Sheets with external services and APIs.

🌐

REST API Data Sync

🔄

Bi-directional Sync

Pull data from APIs and push updates back

🔐

Authentication Handling

Support for API keys, OAuth, and token refresh

// REST API integration example
function syncWithAPI() {
const apiUrl = "https://api.example.com/data";
const apiKey = PropertiesService.getScriptProperties()
.getProperty('API_KEY');
const options = {
'method': 'GET',
'headers': {
'Authorization': `Bearer ${apiKey}`,
'Content-Type': 'application/json'
},
'muteHttpExceptions': true
};
try {
const response = UrlFetchApp.fetch(apiUrl, options);
const data = JSON.parse(response.getContentText());
// Process and write to sheet
writeApiDataToSheet(data);
} catch (error) {
Logger.log(`API Sync Error: ${error}`);
// Send error notification
sendErrorAlert("API Sync Failed", error.toString());
}
}

💬

Slack/Discord Notifications

🔔

Real-time Alerts

Send notifications to team channels for important events

📊

Rich Message Formatting

Include attachments, buttons, and formatted data

// Slack notification example
function sendSlackAlert() {
const webhookUrl = PropertiesService.getScriptProperties()
.getProperty('SLACK_WEBHOOK');
const payload = {
"text": "🚨 Data Update Alert",
"blocks": [
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": `*New data has been processed*\\n` +
`• *Records Updated:* 150\\n` +
`• *Timestamp:* ${new Date().toLocaleString()}`
}
}
]
};
const options = {
'method': 'POST',
'contentType': 'application/json',
'payload': JSON.stringify(payload)
};
UrlFetchApp.fetch(webhookUrl, options);
}

Workflow Automation Templates

Orchestrate complex multi-step processes across Google Workspace.

📝

Document Generation

📄

Template-based Documents

Generate Google Docs from sheet data using templates

🤖

Auto-populate Fields

Replace placeholders with actual data from your sheets

// Document generation example
function generateDocuments() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const data = ss.getRange("A2:F").getValues();
const templateId = "YOUR_GOOGLE_DOC_TEMPLATE_ID";
data.forEach((row, index) => {
if (row[0]) { // Check if row has data
// Copy template
const doc = DriveApp.getFileById(templateId).makeCopy();
const document = DocumentApp.openById(doc.getId());
const body = document.getBody();
// Replace placeholders
body.replaceText("{{NAME}}", row[0]);
body.replaceText("{{EMAIL}}", row[1]);
body.replaceText("{{DATE}}", new Date().toLocaleDateString());
document.saveAndClose();
// Update sheet with document link
ss.getRange(index + 2, 7).setValue(doc.getUrl());
}
});
}

🔄

Approval Workflows

📋

Multi-step Approval

Route requests through multiple approval stages

⏰

Automatic Reminders

Send follow-up emails for pending approvals

// Approval workflow example
function processApprovals() {
const sheet = SpreadsheetApp.getActiveSheet();
const requests = sheet.getRange("A2:G").getValues();
requests.forEach((request, index) => {
const [id, requester, amount, status, approver] = request;
if (status === "Pending" && shouldEscalate(request)) {
// Send reminder to approver
sendApprovalReminder(approver, request);
// Update status
sheet.getRange(index + 2, 4).setValue("Reminder Sent");
}
});
}function shouldEscalate(request) {
const submittedDate = new Date(request[5]); // submission date
const daysPending = (new Date() - submittedDate) / (1000 * 60 * 60 * 24);
return daysPending > 2; // Escalate after 2 days
}

Implementation Resources

Setup Checklist

Step-by-step guide to configure and deploy your chosen template.

View Checklist →

Customization Guide

Learn how to modify templates for your specific use case.

Customize Templates →

Troubleshooting

Common issues and solutions for template implementation.

Get Help →

Advanced Modifications

Extend templates with additional features and integrations.

Learn More →