100 Plus Google Apps Script Exercises Code Examples PDF guide more than 130 pages FREE

100+ Exercises 

Google Apps Script Exercises

Explore what you can do with Google Apps Script in Workspace.  

Useful Code examples to get your started with Google Apps Script

Log a Custom Message

Purpose: Learn how to log a message in Apps Script.

function logMessage() {

Logger.log(‘Hello, Google Apps Script!’);

}

Explanation: This script uses the Logger object to print a custom message to the log. Run it in the Apps Script editor and view the log to see the message.

Send an Email

Purpose: Automatically send an email using GmailApp service.

function sendEmail() {

GmailApp.sendEmail(‘recipient@example.com’, ‘Test Email’, ‘Hello from Google Apps Script!’);

}

Explanation: Replace ‘recipient@example.com’ with the email address of the recipient. This script sends a simple email using the GmailApp service.

Create a Google Doc

Purpose: Programmatically create a Google Document.

function createGoogleDoc() {

DocumentApp.create(‘My New Document’);

}

Explanation: This script creates a new Google Document named “My New Document” in your Google Drive.

Read Data from a Spreadsheet

Purpose: Read and log data from a Google Sheet.

function readSpreadsheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getRange(‘A1:A2’);

var values = range.getValues();

for (var i = 0; i < values.length; i++) {

Logger.log(values[i][0]);

}

}

Explanation: This script reads values from cells A1 to A2 in the active sheet and logs them. Ensure your spreadsheet has data in these cells.

Write Data to a Spreadsheet

Purpose: Write data to a Google Sheet.

function writeSpreadsheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

sheet.getRange(‘B1’).setValue(‘Hello, Sheet!’);

}

Explanation: This script writes the text “Hello, Sheet!” into cell B1 of the active sheet.

Append a Row to a Spreadsheet

Purpose: Append a row of data to the end of a Google Sheet.

function appendRow() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

sheet.appendRow([‘New Data’, ‘More Data’, ‘Even More Data’]);

}

Explanation: This script adds a new row to the bottom of the active sheet with the specified data in each column.

Create a Menu in Google Sheets

Purpose: Add a custom menu to Google Sheets to run your scripts.

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Run Example’, ‘logMessage’)

.addToUi();

}

Explanation: This script creates a custom menu item in Google Sheets called “Custom Menu” with one option, “Run Example”, that, when clicked, runs the logMessage function.

Update Spreadsheet Formatting

Purpose: Change the formatting of cells in a Google Sheet.

function formatCells() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getRange(‘A1:A2’);

range.setBackground(‘yellow’);

range.setFontWeight(‘bold’);

}

Explanation: This script changes the background color and font weight of cells A1 to A2 in the active sheet.

Fetch Data from an API

Purpose: Use UrlFetchApp to make an HTTP GET request to an API.

function fetchData() {

var response = UrlFetchApp.fetch(‘https://api.example.com/data’);

Logger.log(response.getContentText());

}

Explanation: Replace https://api.example.com/data with the URL of the API you wish to fetch data from. This script makes a GET request to the specified URL and logs the response.

Automate Document Creation and Sharing

Purpose: Create a Google Doc and share it with a specific email.

function createAndShareDoc() {

var doc = DocumentApp.create(‘Shared Document’);

DriveApp.getFileById(doc.getId()).addEditor(‘recipient@example.com’);

}

Explanation: This script creates a new Google Document and shares it with the specified email address as an editor. Replace ‘recipient@example.com’ with the email address of the person you want to share the document with.

Batch Update Spreadsheet Cells

Purpose: Learn to perform batch updates in Google Sheets for efficiency.

function batchUpdateCells() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var rangeList = sheet.getRangeList([‘A1’, ‘B2’, ‘C3’]);

rangeList.setValue(‘Updated!’);

}

Explanation: This script updates multiple cells at once in the active sheet, setting their values to “Updated!”. It demonstrates a more efficient way to modify a spreadsheet compared to updating cells one by one.

Generate a Spreadsheet Report from Form Responses

Purpose: Automatically generate a summary report in a new sheet based on Google Forms responses.

function generateFormReport() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

var formResponses = spreadsheet.getSheetByName(‘Form Responses 1’);

var reportSheet = spreadsheet.insertSheet(‘Report Summary’);

// Example: Summarize total responses

var totalResponses = formResponses.getLastRow() – 1; // Adjust for header row

reportSheet.appendRow([‘Total Responses’, totalResponses]);

}

Explanation: This script counts the total number of form responses (excluding the header row) and writes this summary to a new sheet. Adjust the name ‘Form Responses 1’ if your form responses sheet has a different name.

Schedule Email Reminders from Spreadsheet Data

Purpose: Use time-driven triggers to send daily email reminders based on a spreadsheet schedule.

function scheduleEmailReminders() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Schedule’);

var dataRange = sheet.getDataRange();

var data = dataRange.getValues();

data.forEach(function(row, index) {

if (index === 0) return; // Skip header row

var email = row[0]; // Assuming email addresses are in the first column

var task = row[1]; // Assuming tasks are in the second column

var message = ‘Reminder: You have to ‘ + task;

var subject = ‘Daily Task Reminder’;

GmailApp.sendEmail(email, subject, message);

});

}

Explanation: This script assumes you have a spreadsheet named ‘Schedule’ with email addresses in the first column and tasks in the second. It sends an email reminder for each task. Set up a time-driven trigger in the Apps Script editor to run this function daily.

Convert Spreadsheet Data to JSON

Purpose: Create a JSON representation of a spreadsheet’s data for use in web applications or APIs.

function convertSheetToJson() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var data = sheet.getDataRange().getValues();

var headers = data.shift(); // First row as headers

var jsonData = [];

data.forEach(function(row) {

var obj = {};

headers.forEach(function(header, i) {

obj[header] = row[i];

});

jsonData.push(obj);

});

Logger.log(JSON.stringify(jsonData));

}

Explanation: This script converts the active sheet’s data into a JSON array, where each row becomes an object with keys derived from the header row. This is useful for integrating spreadsheet data with web applications.

Automate Calendar Event Creation from Spreadsheet

Purpose: Create Google Calendar events automatically from spreadsheet data.

function createCalendarEvents() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var dataRange = sheet.getDataRange();

var data = dataRange.getValues();

var calendar = CalendarApp.getDefaultCalendar();

data.forEach(function(row, index) {

if (index === 0) return; // Skip header row

var eventTitle = row[0];

var startDate = new Date(row[1]);

var endDate = new Date(row[2]);

calendar.createEvent(eventTitle, startDate, endDate);

});

}

Explanation: Assumes the first column has the event title, the second has the start date, and the third has the end date. This script creates a calendar event for each row in the active sheet.

Manipulate Google Docs Content

Purpose: Programmatically modify the content of a Google Document.

function updateGoogleDoc() {

var doc = DocumentApp.openById(‘YOUR_DOCUMENT_ID’);

var body = doc.getBody();

body.appendParagraph(‘This is a new paragraph added by Google Apps Script.’);

doc.saveAndClose();

}

Explanation: Replace ‘YOUR_DOCUMENT_ID’ with the actual ID of your document. This script adds a new paragraph to the end of the document.

Extract Email Addresses from a Document

Purpose: Write a script to find and log all email addresses in a Google Document.

function extractEmails() {

var doc = DocumentApp.getActiveDocument();

var text = doc.getBody().getText();

var emails = text.match(/[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}/g);

Logger.log(emails);

}

Explanation: This script extracts all occurrences of email addresses from the active Google Document’s text using a regular expression and logs them to the Apps Script log.

Sync Spreadsheet with Google Calendar

Purpose: Synchronize events in a Google Calendar with a spreadsheet, creating new events and updating existing ones.

function syncCalendarWithSpreadsheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var eventsRange = sheet.getDataRange();

var events = eventsRange.getValues();

var calendar = CalendarApp.getDefaultCalendar();

events.forEach(function(event, index) {

if (index === 0) return; // Skip header row

var title = event[0];

var startTime = new Date(event[1]);

var endTime = new Date(event[2]);

var options = { description: event[3] }; // Additional details in fourth column

calendar.createEvent(title, startTime, endTime, options);

});

}

Explanation: This script assumes the spreadsheet contains event titles in the first column, start times in the second, end times in the third, and optional descriptions in the fourth. It creates or updates events in the default calendar accordingly.

Automatically Add Spreadsheet Data to a Google Doc

Purpose: Append data from a spreadsheet to a Google Doc as a formatted list.

function appendSpreadsheetDataToDoc() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var values = sheet.getDataRange().getValues();

var doc = DocumentApp.openById(‘YOUR_DOCUMENT_ID’);

var body = doc.getBody();

values.forEach(function(row) {

body.appendParagraph(row.join(‘, ‘));

});

doc.saveAndClose();

}

Explanation: This script reads data from the active spreadsheet and appends each row as a comma-separated list to a Google Document. Replace ‘YOUR_DOCUMENT_ID’ with your document’s ID.

Create a Google Sheets Dashboard

Purpose: Use Google Sheets to create a simple dashboard that summarizes data from another sheet.

function createDashboard() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sourceSheet = ss.getSheetByName(‘Data’);

var dashboardSheet = ss.getSheetByName(‘Dashboard’) || ss.insertSheet(‘Dashboard’);

var data = sourceSheet.getDataRange().getValues();

// Example: Sum and average of the first column

var sum = 0;

data.forEach(function(row, index) {

if (index > 0) sum += row[0]; // Assuming data starts from second row

});

var average = sum / (data.length – 1);

dashboardSheet.clear(); // Clear previous data

dashboardSheet.appendRow([‘Sum’, sum]);

dashboardSheet.appendRow([‘Average’, average]);

}

Explanation: This script calculates the sum and average of the first column’s values from a ‘Data’ sheet and displays the results in a ‘Dashboard’ sheet. Adjust the column index as necessary for different data structures.

Monitor Sheet Changes and Send Notifications

Purpose: Send an email notification when a specific range in a spreadsheet is edited.

function onEdit(e) {

var range = e.range;

// Check if the edit is in a specific range (e.g., A1:A10)

if (range.getSheet().getName() === ‘Sheet1’ && range.getRow() >= 1 && range.getRow() <= 10 && range.getColumn() === 1) {

var emailAddress = ‘your_email@example.com’;

var subject = ‘Spreadsheet Edit Notification’;

var message = ‘A cell in A1:A10 was edited. New value: ‘ + range.getValue();

MailApp.sendEmail(emailAddress, subject, message);

}

}

Explanation: Replace ‘your_email@example.com’ with your actual email address. This script uses the onEdit trigger to monitor edits within a specific range (A1:A10) on ‘Sheet1’ and sends an email notification when changes occur.

Import JSON Data into a Spreadsheet

Purpose: Fetch data from a public JSON API and import it into a Google Sheet.

function importJSONData() {

var response = UrlFetchApp.fetch(‘https://api.example.com/data’);

var json = JSON.parse(response.getContentText());

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

json.forEach(function(item, index) {

sheet.appendRow([item.id, item.name, item.value]); // Adjust based on JSON structure

});

}

Explanation: This script fetches data from a specified JSON API endpoint, parses the JSON, and then iterates over the array of items to append each item’s details as a new row in the active sheet. Replace the URL and adjust the row contents according to your JSON structure.

Create Custom Spreadsheet Function

Purpose: Develop a custom function for Google Sheets that can be used as a formula within the spreadsheet.

/**

* Converts meters to feet.

*

* @param {number} meters The value in meters to convert.

* @return The converted value in feet.

* @customfunction

*/

function METERSTOFEET(meters) {

if (typeof meters !== ‘number’) return ‘Invalid input’;

return meters * 3.28084;

}

Explanation: This script defines a custom function METERSTOFEET that converts meters to feet. Once defined, you can use =METERSTOFEET(A1) in your Google Sheets, where A1 contains the value in meters.

Update Chart in Spreadsheet Based on Data

Purpose: Automatically update a chart in a Google Sheet when data changes.

function updateChart() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var chart = sheet.getCharts()[0]; // Assumes there is at least one chart in the sheet

var range = sheet.getRange(‘A1:B10’); // Specify the range that contains new data for the chart

chart = chart.modify()

.setOption(‘title’, ‘Updated Chart Title’)

.setRange(range.getA1Notation())

.build();

sheet.updateChart(chart);

}

Explanation: This script modifies the first chart in the active sheet, updating its title and data range. Adjust the range and options as needed for your specific chart and data.

Parse and Respond to Emails

Purpose: Search for specific emails in Gmail and automatically respond.

function parseAndRespondToEmails() {

var query = ‘subject:”action required” newer_than:2d’;

var threads = GmailApp.search(query);

threads.forEach(function(thread) {

var messages = thread.getMessages();

var message = messages[messages.length – 1]; // Get the last message in the thread

GmailApp.sendEmail(message.getFrom(), ‘Re: ‘ + message.getSubject(), ‘Your request has been processed.’);

});

}

Explanation: This script searches for emails with a subject containing “action required” received within the last 2 days and sends a response to the sender of the last message in each thread.

Automate Document Approval Process

Purpose: Create a simple document approval process using Google Docs and Gmail.

function requestApproval(docId, approverEmail) {

var doc = DocumentApp.openById(docId);

var docUrl = doc.getUrl();

var subject = ‘Approval Needed: ‘ + doc.getName();

var message = ‘Please review the following document and approve: ‘ + docUrl;

GmailApp.sendEmail(approverEmail, subject, message);

}

Explanation: Replace docId and approverEmail with the document’s ID and the approver’s email address, respectively. This script sends an email to the approver with a link to the document for review.

Bulk Create Google Calendar Events from Spreadsheet

Purpose: Read event details from a spreadsheet and create multiple Google Calendar events in bulk.

function createBulkCalendarEvents() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Events’);

var events = sheet.getDataRange().getValues();

var calendar = CalendarApp.getDefaultCalendar();

// Skip header row with slice(1)

events.slice(1).forEach(function(row) {

var title = row[0];

var startTime = new Date(row[1]);

var endTime = new Date(row[2]);

var options = {

description: row[3],

location: row[4]

};

calendar.createEvent(title, startTime, endTime, options);

});

}

Explanation: This script assumes your ‘Events’ sheet has columns for the event title, start time, end time, description, and location, in that order, starting from the first row after the header. It creates events in the default Google Calendar based on this data.

Auto-archive Gmail Messages with Specific Label

Purpose: Find Gmail messages with a specific label and archive them.

function archiveLabelledMessages() {

var label = GmailApp.getUserLabelByName(“To Archive”);

var threads = label.getThreads();

for (var i = 0; i < threads.length; i++) {

threads[i].moveToArchive();

}

}

Explanation: This script archives all email threads with the label “To Archive.” Replace “To Archive” with the name of your specific label.

Dynamically Update Google Sheets Chart Titles

Purpose: Change the title of all charts in a Google Sheet based on cell content.

function updateChartTitles() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var charts = sheet.getCharts();

var title = sheet.getRange(“A1”).getValue(); // Assuming the new title is in cell A1

charts.forEach(function(chart) {

var updatedChart = chart.modify().setOption(‘title’, title).build();

sheet.updateChart(updatedChart);

});

}

Explanation: This script updates all chart titles in the active sheet to match the content of cell A1. Adjust the cell reference as needed for different titles.

Sync Google Sheets with External Database

Purpose: Import data from an external database API into a Google Sheet.

function syncWithExternalDatabase() {

var response = UrlFetchApp.fetch(‘https://api.example.com/data’);

var data = JSON.parse(response.getContentText());

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Clear existing data

sheet.clearContents();

// Assuming data is an array of objects

data.forEach(function(item, index) {

// Adjust according to the structure of your data

sheet.appendRow([item.id, item.name, item.details]);

});

}

Explanation: Replace https://api.example.com/data with your actual API endpoint. This script fetches data from an external source and populates a Google Sheet with it, clearing any existing content first.

Generate PDFs from Google Docs Template

Purpose: Create a PDF from a Google Docs template and populate it with data from a spreadsheet.

function generatePDFs() {

var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Data”);

var dataRange = dataSheet.getDataRange();

var data = dataRange.getValues();

var templateId = “TEMPLATE_DOCUMENT_ID”;

data.slice(1).forEach(function(row) {

var docCopy = DriveApp.getFileById(templateId).makeCopy();

var doc = DocumentApp.openById(docCopy.getId());

var body = doc.getBody();

// Replace placeholder text with actual data

body.replaceText(“{{Name}}”, row[0]);

body.replaceText(“{{Date}}”, row[1]);

doc.saveAndClose();

// Convert to PDF

var pdf = DriveApp.getFileById(doc.getId()).getAs(“application/pdf”);

var pdfName = row[0] + “_Document.pdf”;

DriveApp.createFile(pdf).setName(pdfName);

// Optional: delete the Docs copy

DriveApp.getFileById(doc.getId()).setTrashed(true);

});

}

Explanation: Replace “TEMPLATE_DOCUMENT_ID” with the ID of your Google Docs template. This script assumes your ‘Data’ sheet has a name in the first column and a date in the second column, and your template document contains {{Name}} and {{Date}} placeholders. It creates a PDF for each row of data.

Extract and Summarize Key Information from Emails

Purpose: Search for emails matching specific criteria and summarize the information in a spreadsheet.

function summarizeEmails() {

var query = ‘from:no-reply@example.com subject:”Order Confirmation”‘;

var threads = GmailApp.search(query);

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Email Summaries”);

threads.forEach(function(thread) {

var messages = thread.getMessages();

var message = messages[messages.length – 1]; // Get the last message

var subject = message.getSubject();

var date = message.getDate();

var snippet = message.getSnippet();

sheet.appendRow([date, subject, snippet]);

});

}

Explanation: Replace from:no-reply@example.com subject:”Order Confirmation” with your actual search criteria. This script finds emails that match the query, then logs the date, subject, and snippet of the last message in each thread to a spreadsheet named “Email Summaries.”

Automate Meeting Notes Template Creation

Purpose: Create a new Google Docs meeting notes template for each scheduled meeting in Google Calendar.

function createMeetingNotes() {

var calendarId = ‘primary’; // Use ‘primary’ or specific calendar ID

var now = new Date();

var end = new Date(now.getTime() + 7 * 24 * 60 * 60 * 1000); // Next 7 days

var events = CalendarApp.getCalendarById(calendarId).getEvents(now, end);

events.forEach(function(event) {

var doc = DocumentApp.create(event.getTitle() + ‘ Meeting Notes’);

var body = doc.getBody();

body.appendParagraph(‘Meeting Title: ‘ + event.getTitle());

body.appendParagraph(‘Date: ‘ + event.getStartTime());

// Add more structure as needed

Logger.log(doc.getUrl());

});

}

Explanation: This script creates a new Google Document for each event on the user’s primary calendar for the next 7 days, pre-filled with a basic meeting notes template including the meeting title and date. Adjust the calendar ID and template structure as needed.

Monitor Spreadsheet for Invalid Data Entries

Purpose: Highlight cells in a Google Sheet that contain invalid data based on specific criteria.

function highlightInvalidData() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getDataRange();

var values = range.getValues();

values.forEach(function(row, rowIndex) {

row.forEach(function(cell, columnIndex) {

// Example criteria: highlight non-numeric entries in column 2

if (columnIndex === 1 && isNaN(cell)) {

var cellRange = sheet.getRange(rowIndex + 1, columnIndex + 1);

cellRange.setBackground(‘red’);

}

});

});

}

Explanation: This script scans the active sheet for non-numeric entries in the second column and highlights those cells in red. Modify the criteria to fit your specific validation needs.

Auto-generate and Email Custom Reports

Purpose: Generate a custom report from spreadsheet data and email it as a PDF.

function emailCustomReports() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Report Data”);

var range = sheet.getDataRange();

var values = range.getValues();

var reportDoc = DocumentApp.create(“Custom Report”);

var body = reportDoc.getBody();

values.forEach(function(row) {

body.appendParagraph(row.join(“, “));

});

reportDoc.saveAndClose();

// Convert document to PDF

var pdf = DriveApp.getFileById(reportDoc.getId()).getAs(“application/pdf”);

var recipients = “recipient@example.com”; // Change to actual recipient email address

var subject = “Custom Report”;

var bodyEmail = “Please find the attached custom report.”;

MailApp.sendEmail(recipients, subject, bodyEmail, {attachments: [pdf]});

// Optional: Clean up by deleting the temporary report document

DriveApp.getFileById(reportDoc.getId()).setTrashed(true);

}

Explanation: This script takes data from a “Report Data” sheet, creates a Google Document with that data, converts the document into a PDF, and then emails the PDF to a specified recipient. It cleans up by trashing the temporary Google Document after emailing. Adjust the sheet name, recipient, and report content as necessary.

Automatically Label and Categorize Emails

Purpose: Scan incoming emails for specific keywords and automatically apply labels.

function labelAndCategorizeEmails() {

var inboxThreads = GmailApp.getInboxThreads();

var labelName = “Categorized”;

var label = GmailApp.getUserLabelByName(labelName) || GmailApp.createLabel(labelName);

inboxThreads.forEach(function(thread) {

var messages = thread.getMessages();

var message = messages[messages.length – 1]; // Check the last message in the thread

var body = message.getPlainBody();

// Example: Label emails containing “invoice”

if (body.indexOf(“invoice”) !== -1) {

thread.addLabel(label);

}

});

}

Explanation: This script checks the last message of each email thread in the user’s inbox. If the message body contains the keyword “invoice,” it applies a label named “Categorized” to the thread. Adjust the keyword and label name as necessary for your use case.

Sync Contacts from Spreadsheet to Google Contacts

Purpose: Create or update Google Contacts based on data in a Google Sheet.

function syncContacts() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Contacts’);

var rows = sheet.getDataRange().getValues();

rows.slice(1).forEach(function(row) { // Assuming first row is header

var fullName = row[0];

var email = row[1];

var phone = row[2];

var contacts = ContactsApp.getContactsByName(fullName);

if (contacts.length === 0) {

var newContact = ContactsApp.createContact(fullName, ”, email);

newContact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone);

} else {

var contact = contacts[0];

contact.setEmails([]);

contact.addEmail(ContactsApp.Field.WORK_EMAIL, email);

contact.setPhones([]);

contact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone);

}

});

}

Explanation: This script reads names, emails, and phone numbers from a ‘Contacts’ sheet, checks if each contact already exists in Google Contacts, and either updates their details or creates a new contact.

Automate Folder Structure Creation in Google Drive

Purpose: Generate a predefined folder structure within Google Drive based on project names listed in a spreadsheet.

function createProjectFolders() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Projects’);

var projects = sheet.getRange(‘A2:A’ + sheet.getLastRow()).getValues();

var parentFolder = DriveApp.getFolderById(‘PARENT_FOLDER_ID’);

projects.forEach(function(project) {

var projectName = project[0];

if (!projectName) return;

var projectFolder = parentFolder.createFolder(projectName);

// Create sub-folders within each project folder

[‘Documents’, ‘Images’, ‘Data’].forEach(function(subFolderName) {

projectFolder.createFolder(subFolderName);

});

});

}

Explanation: This script reads project names from the ‘Projects’ sheet and creates a main folder for each project within a specified parent folder in Google Drive. It also creates three sub-folders (‘Documents’, ‘Images’, ‘Data’) within each project folder.

Bulk Apply Conditional Formatting in Google Sheets

Purpose: Apply conditional formatting rules to multiple ranges in a Google Sheet programmatically.

function applyConditionalFormatting() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var ranges = [‘A1:A10’, ‘B1:B10’, ‘C1:C10’]; // Example ranges to format

ranges.forEach(function(rangeA1Notation) {

var range = sheet.getRange(rangeA1Notation);

var rule = SpreadsheetApp.newConditionalFormatRule()

.whenNumberGreaterThan(0)

.setBackground(‘#FFFF00’) // Yellow background for positive numbers

.setRanges([range])

.build();

var rules = sheet.getConditionalFormatRules();

rules.push(rule);

sheet.setConditionalFormatRules(rules);

});

}

Explanation: This script applies a conditional formatting rule to highlight cells in yellow if their value is greater than 0 in specified ranges within the active sheet.

Track Google Form Responses in Real-Time

Purpose: Use an Apps Script trigger to perform actions each time a Google Form response is submitted.

function onFormSubmit(e) {

var responseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Responses’);

var itemResponses = e.response.getItemResponses();

var responseData = [];

itemResponses.forEach(function(itemResponse) {

responseData.push(itemResponse.getResponse());

});

// Add timestamp

responseData.unshift(new Date());

responseSheet.appendRow(responseData);

}

Explanation: Attach this script to a Google Form’s response submission trigger. It collects responses as they are submitted, adding them to a ‘Responses’ sheet with a timestamp.

Automate Google Slides Presentation Creation

Purpose: Generate a Google Slides presentation from a template, customizing it with data from a spreadsheet.

function createPresentationFromTemplate() {

var templateId = ‘TEMPLATE_PRESENTATION_ID’;

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data’);

var rows = sheet.getDataRange().getValues();

var templatePresentation = SlidesApp.openById(templateId);

var presentationCopy = DriveApp.getFileById(templatePresentation.getId()).makeCopy();

var presentation = SlidesApp.openById(presentationCopy.getId());

rows.slice(1).forEach(function(row, index) {

var slide = presentation.getSlides()[index];

var shapes = slide.getShapes();

shapes.forEach(function(shape) {

var text = shape.getText();

text.setText(row[0]); // Assuming the data to replace is in the first column

});

});

Logger.log(‘Presentation created: ‘ + presentation.getUrl());

}

Explanation: This script clones a Google Slides template and updates text in each slide based on data from the ‘Data’ sheet, assuming each row’s first column contains the text for each slide.

Monitor Google Drive Changes

Purpose: Log changes in a specific Google Drive folder, such as new files added or removed.

function logDriveFolderChanges() {

var folderId = ‘FOLDER_ID’;

var folder = DriveApp.getFolderById(folderId);

var files = folder.getFiles();

var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Change Log’);

while (files.hasNext()) {

var file = files.next();

logSheet.appendRow([new Date(), ‘New file’, file.getName(), file.getUrl()]);

}

// Additional logic to track and log removed files would go here

}

Explanation: This script lists all files in a specified folder and logs their names and URLs to a ‘Change Log’ sheet, marking each entry with a timestamp. Implementing removal tracking would require storing file states and comparing over time.

Automate Email Digests from Spreadsheet Data

Purpose: Send a weekly email digest summarizing data from a Google Sheet.

function sendWeeklyDigest() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Weekly Summary’);

var range = sheet.getDataRange();

var values = range.getValues();

var digestContent = values.map(function(row) { return row.join(‘, ‘); }).join(‘\n’);

var emailAddress = ‘recipient@example.com’;

var subject = ‘Weekly Digest’;

var body = ‘Here is your weekly digest:\n\n’ + digestContent;

MailApp.sendEmail(emailAddress, subject, body);

}

Explanation: This script compiles data from the ‘Weekly Summary’ sheet into a simple text format and emails it to a specified address as a weekly digest. Adjust the sheet name and recipient as necessary.

Generate and Share Google Docs Reports

Purpose: Create Google Docs reports from a template, filling in data from a spreadsheet, and share them with a list of recipients.

function generateAndShareReports() {

var templateId = ‘TEMPLATE_DOC_ID’;

var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Report Data’);

var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Email List’);

var data = dataSheet.getDataRange().getValues();

var emails = emailSheet.getRange(‘A2:A’ + emailSheet.getLastRow()).getValues().flat();

data.slice(1).forEach(function(row) {

var documentCopy = DriveApp.getFileById(templateId).makeCopy();

var document = DocumentApp.openById(documentCopy.getId());

var body = document.getBody();

// Example: Replace placeholders in the document

body.replaceText(‘{{Name}}’, row[0]);

body.replaceText(‘{{Date}}’, row[1]);

document.saveAndClose();

// Share the document with each email in the list

emails.forEach(function(email) {

documentCopy.addViewer(email);

});

});

}

Explanation: This script creates a copy of a specified Google Docs template for each row of data in the ‘Report Data’ sheet, replaces placeholders with actual data, and then shares the document with viewers listed in the ‘Email List’ sheet.

Auto-update Spreadsheet Data from External API

Purpose: Fetch and update a Google Sheet with the latest data from an external API at regular intervals.

function updateDataFromAPI() {

var apiURL = ‘https://api.example.com/latest’;

var response = UrlFetchApp.fetch(apiURL);

var jsonData = JSON.parse(response.getContentText());

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘External Data’);

sheet.clear(); // Clear existing data

sheet.appendRow([‘ID’, ‘Name’, ‘Value’]); // Assuming JSON data structure

jsonData.forEach(function(item) {

sheet.appendRow([item.id, item.name, item.value]);

});

}

Explanation: This script clears the ‘External Data’ sheet and fetches new data from an external API, appending each item as a row. It’s useful for keeping a Google Sheet updated with the latest external data. Set up time-driven triggers in Apps Script to run this function periodically

Summarize Google Sheets Data in an Email

Purpose: Compile a summary from a Google Sheet and email it automatically.

function emailSheetSummary() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

const range = sheet.getDataRange();

const values = range.getValues();

let summary = ‘Sheet Summary:\n\n’;

// Example: Summarize each row

values.forEach((row, index) => {

if (index === 0) return; // Skip header

summary += `Row ${index + 1}: ${row.join(‘, ‘)}\n`;

});

MailApp.sendEmail(‘recipient@example.com’, ‘Your Google Sheet Summary’, summary);

}

Explanation: This script creates a simple summary of the active Google Sheet by iterating over each row of data and then sends this summary to a specified email address.

Automate Google Calendar Event Reminders in Sheets

Purpose: Use a Google Sheet to track events and automatically create email reminders.

function createEventReminders() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Events”);

const events = sheet.getDataRange().getValues();

const today = new Date();

events.forEach(([title, eventDate, email], index) => {

if (index === 0 || !email) return; // Skip header or entries without email

const date = new Date(eventDate);

if (date.toDateString() === today.toDateString()) {

MailApp.sendEmail(email, ‘Event Reminder’, `Reminder for event: ${title} on ${date.toDateString()}`);

}

});

}

Explanation: This script checks a specified “Events” sheet daily for any events occurring on the current date and sends a reminder email to the associated email address.

Track Spreadsheet Edits with Timestamps

Purpose: Log every edit made in a Google Sheet, including the cell changed, the new value, and a timestamp.

function onEdit(e) {

const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Edit Log’);

const range = e.range;

const newValue = e.value;

const timestamp = new Date();

logSheet.appendRow([timestamp, range.getA1Notation(), newValue]);

}

Explanation: This script uses the onEdit trigger to log edits to a sheet named “Edit Log,” recording the timestamp, cell reference, and the new value for each edit.

Import RSS Feed into Google Sheets

Purpose: Fetch and display content from an RSS feed in a Google Sheet.

function importRSSFeed() {

const url = ‘https://example.com/feed’;

const response = UrlFetchApp.fetch(url);

const xml = response.getContentText();

const document = XmlService.parse(xml);

const entries = document.getRootElement().getChildren(‘channel’)[0].getChildren(‘item’);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘RSS Feed’);

sheet.clear(); // Clear existing entries

sheet.appendRow([‘Title’, ‘Link’, ‘Description’]); // Header

entries.forEach(entry => {

const title = entry.getChild(‘title’).getText();

const link = entry.getChild(‘link’).getText();

const description = entry.getChild(‘description’).getText();

sheet.appendRow([title, link, description]);

});

}

Explanation: This script fetches data from an RSS feed, parses the XML to extract the title, link, and description of each item, and writes this information to a sheet named “RSS Feed.”

Batch Convert Google Docs to PDF

Purpose: Convert multiple Google Docs in a folder to PDFs and save them in another folder.

function convertDocsToPDFs() {

const sourceFolder = DriveApp.getFolderById(‘SOURCE_FOLDER_ID’);

const targetFolder = DriveApp.getFolderById(‘TARGET_FOLDER_ID’);

const files = sourceFolder.getFilesByType(MimeType.GOOGLE_DOCS);

while (files.hasNext()) {

const file = files.next();

const doc = DocumentApp.openById(file.getId());

const pdfContent = DriveApp.getFileById(doc.getId()).getAs(‘application/pdf’);

targetFolder.createFile(pdfContent).setName(file.getName() + ‘.pdf’);

}

}

Explanation: This script iterates through all Google Docs in a specified source folder, converts each to a PDF, and saves the PDFs in a designated target folder.

Sync Google Sheet Data to a Shared Calendar

Purpose: Read event details from a spreadsheet and update a shared Google Calendar.

function syncSheetToCalendar() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Events’);

const calendarId = ‘SHARED_CALENDAR_ID’;

const calendar = CalendarApp.getCalendarById(calendarId);

const events = sheet.getDataRange().getValues();

events.slice(1).forEach(([title, startDate, endDate]) => {

calendar.createEvent(title, new Date(startDate), new Date(endDate));

});

}

Explanation: This script reads event details from the “Events” sheet, creating events in a specified shared Google Calendar based on the title, start date, and end date provided.

Aggregate Gmail Labels Count in Sheets

Purpose: Count the number of emails under each label in Gmail and summarize this data in a Google Sheet.

function summarizeGmailLabels() {

const labels = GmailApp.getUserLabels();

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Gmail Summary’);

sheet.clear();

sheet.appendRow([‘Label’, ‘Email Count’]);

labels.forEach(label => {

const count = label.getThreads().length;

sheet.appendRow([label.getName(), count]);

});

}

Explanation: This script retrieves all user-created labels in Gmail, counts the number of email threads associated with each label, and summarizes this data in a “Gmail Summary” sheet.

Auto-Generate Google Forms from Sheet Data

Purpose: Create a Google Form with questions generated based on the data in a Google Sheet.

function generateFormFromSheet() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Questions’);

const questions = sheet.getDataRange().getValues();

const form = FormApp.create(‘New Form’);

questions.slice(1).forEach(([question, type, options]) => { // Skip header

if (type === ‘MCQ’) {

const item = form.addMultipleChoiceItem();

item.setTitle(question);

item.setChoiceValues(options.split(‘,’));

} else if (type === ‘Text’) {

form.addTextItem().setTitle(question);

}

});

}

Explanation: This script reads a “Questions” sheet containing question text, type (e.g., MCQ for multiple-choice questions, Text for text input), and options for MCQs. It then creates a Google Form with these questions.

Generate Monthly Expense Reports from Sheets

Purpose: Compile and email a monthly expense report based on data from a Google Sheet.

function sendMonthlyExpenseReport() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Expenses’);

const range = sheet.getDataRange();

const values = range.getValues();

let report = ‘Monthly Expense Report:\n\n’;

// Example: Assume date and amount are in columns A and B

values.slice(1).forEach(([date, amount]) => { // Skip header

report += `Date: ${date}, Amount: ${amount}\n`;

});

MailApp.sendEmail(‘recipient@example.com’, ‘Monthly Expense Report’, report);

}

Explanation: This script aggregates expense data from an “Expenses” sheet, formatting it into a simple report which is then emailed to a specified address.

Monitor and Alert for Spreadsheet Changes

Purpose: Send an alert email if specific cells in a Google Sheet are modified.

function onEdit(e) {

const monitoredRange = ‘A1:B10’; // Specify the range to monitor

const range = e.range.getA1Notation();

if (monitoredRange.includes(range)) {

MailApp.sendEmail(‘recipient@example.com’, ‘Alert: Spreadsheet Change’, `Change detected in cell: ${range}`);

}

}

Explanation: Leveraging the onEdit trigger, this script monitors edits within a specified range of a Google Sheet. If a change is detected within this range, it sends an alert email to a designated recipient.

Clean and Format Imported Data

Purpose: Automatically clean and format newly imported data into a Google Sheet.

function formatImportedData() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Imported Data’);

const range = sheet.getDataRange();

const values = range.getValues();

values.forEach((row, rowIndex) => {

row.forEach((cell, columnIndex) => {

// Example: Trim whitespace and capitalize first letter

const trimmed = cell.trim();

const formatted = trimmed.charAt(0).toUpperCase() + trimmed.slice(1).toLowerCase();

sheet.getRange(rowIndex + 1, columnIndex + 1).setValue(formatted);

});

});

}

Explanation: This script iterates over every cell in an “Imported Data” sheet, trims leading and trailing whitespace from each cell’s content, and capitalizes the first letter of each entry, standardizing the data format.

Schedule Daily Summary Reports of Google Forms Responses

Purpose: Generate and email a daily summary report of responses to a Google Form collected in a Google Sheet.

function sendDailyFormResponsesSummary() {

const formResponsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Form Responses 1’);

const responses = formResponsesSheet.getDataRange().getValues();

let summary = ‘Daily Summary of Form Responses:\n\n’;

// Skip header and compile responses

responses.slice(1).forEach((response, index) => {

summary += `Response ${index + 1}: ${response.join(‘, ‘)}\n`;

});

MailApp.sendEmail(‘recipient@example.com’, ‘Daily Form Responses Summary’, summary);

}

Explanation: This script compiles all responses from a Google Form (stored in the first sheet typically named ‘Form Responses 1’) into a summary and emails it daily. Implement this with a time-driven trigger for automation.

Auto-update Google Sheets with New Google Calendar Events

Purpose: Automatically add new events from a specified Google Calendar to a Google Sheet.

function updateSheetWithNewCalendarEvents() {

const calendarId = ‘YOUR_CALENDAR_ID’;

const calendar = CalendarApp.getCalendarById(calendarId);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Calendar Events’);

const events = calendar.getEvents(new Date(), new Date(new Date().getTime() + (7 * 24 * 60 * 60 * 1000))); // Next 7 days

events.forEach(event => {

sheet.appendRow([event.getTitle(), event.getStartTime(), event.getEndTime()]);

});

}

Explanation: This script fetches events scheduled within the next week from a specified Google Calendar and logs them into a “Calendar Events” sheet. Replace ‘YOUR_CALENDAR_ID’ with the actual ID of your calendar.

Merge Data from Multiple Sheets into One Summary Sheet

Purpose: Combine data from several sheets within a spreadsheet into one summary sheet.

function mergeDataFromMultipleSheets() {

const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

const summarySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Summary’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Summary’);

summarySheet.clear(); // Clear existing data

sheets.forEach(sheet => {

if (sheet.getName() !== ‘Summary’) {

const data = sheet.getDataRange().getValues();

data.forEach(row => summarySheet.appendRow(row));

}

});

}

Explanation: This script loops through all sheets in the active spreadsheet, excluding a sheet named “Summary,” and appends their data to the “Summary” sheet, effectively merging the data.

Analyze and Categorize Email Sentiments with Google Natural Language API

Purpose: Analyze the sentiment of received emails and categorize them based on positivity or negativity.

function categorizeEmailSentiments() {

const emails = GmailApp.search(‘label:inbox’, 0, 10);

const positiveLabel = GmailApp.createLabel(‘Positive Sentiments’);

const negativeLabel = GmailApp.createLabel(‘Negative Sentiments’);

emails.forEach(email => {

const message = email.getMessages()[0].getPlainBody();

const sentimentScore = analyzeSentiment(message); // Assume this function calls the Google Natural Language API

if (sentimentScore > 0) {

email.addLabel(positiveLabel);

} else {

email.addLabel(negativeLabel);

}

});

}

// Placeholder for sentiment analysis function

function analyzeSentiment(text) {

// This function would call the Google Natural Language API and return the sentiment score.

// For simplicity in this example, it just returns a dummy value.

return Math.random() * 2 – 1; // Random score between -1 and 1

}

Explanation: This script, through a hypothetical analyzeSentiment function, analyzes the sentiment of the first 10 emails in the inbox using the Google Natural Language API (implementation assumed) and labels them as positive or negative based on the sentiment score.

Automate Meeting Room Booking in Google Calendar

Purpose: Search for and book available meeting rooms for events directly from a Google Sheet.

function bookMeetingRoom() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Meetings’);

const meetings = sheet.getDataRange().getValues();

const calendarId = ‘ROOM_CALENDAR_ID’; // Use the calendar ID of your meeting room

const calendar = CalendarApp.getCalendarById(calendarId);

meetings.slice(1).forEach(([title, startTime, endTime]) => {

calendar.createEvent(title, new Date(startTime), new Date(endTime));

});

}

Explanation: This script reads meeting details (title, start time, end time) from a “Meetings” sheet and books rooms by creating events in the specified meeting room’s Google Calendar.

Generate Google Docs Report from Google Sheets Data

Purpose: Create a detailed report in Google Docs format using data aggregated from a Google Sheet.

function generateDocsReportFromSheet() {

const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data’);

const data = dataSheet.getDataRange().getValues();

const doc = DocumentApp.create(‘Sheet Data Report’);

const body = doc.getBody();

data.forEach((row, index) => {

if (index === 0) { // Header

body.appendParagraph(row.join(‘ | ‘)).setHeading(DocumentApp.ParagraphHeading.HEADING1);

} else {

body.appendParagraph(row.join(‘, ‘));

}

});

Logger.log(‘Report created: ‘ + doc.getUrl());

}

Explanation: This script generates a Google Docs report titled “Sheet Data Report” with data from the “Data” sheet. The first row is treated as a header and formatted differently from the rest of the data.

Auto-Label High-Priority Emails in Gmail

Purpose: Scan incoming emails for certain keywords and automatically apply a “High Priority” label.

function labelHighPriorityEmails() {

const keywords = [‘urgent’, ‘asap’, ‘immediate’];

const highPriorityLabel = GmailApp.createLabel(‘High Priority’);

const threads = GmailApp.getInboxThreads(0, 50);

threads.forEach(thread => {

const content = thread.getMessages()[0].getPlainBody().toLowerCase();

if (keywords.some(keyword => content.includes(keyword))) {

thread.addLabel(highPriorityLabel);

}

});

}

Explanation: This script checks the first 50 threads in the user’s inbox for emails containing any of the specified keywords and labels them as “High Priority.”

Track Google Form Submission Trends Over Time

Purpose: Analyze and visualize submission trends from Google Forms data in a Google Sheet.

function trackFormSubmissionTrends() {

const formResponsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Form Responses 1’);

const responses = formResponsesSheet.getDataRange().getValues();

const trendsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Submission Trends’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Submission Trends’);

const dateCounts = responses.slice(1).reduce((acc, row) => {

const date = new Date(row[0]).toDateString(); // Assuming timestamp is in the first column

acc[date] = (acc[date] || 0) + 1;

return acc;

}, {});

trendsSheet.clear();

trendsSheet.appendRow([‘Date’, ‘Submissions’]);

Object.keys(dateCounts).forEach(date => {

trendsSheet.appendRow([date, dateCounts[date]]);

});

}

Explanation: This script calculates the number of form submissions per day based on the timestamps recorded in a “Form Responses 1” sheet and summarizes this data in a “Submission Trends” sheet, providing insights into submission patterns over time.

Summarize Google Docs Comments in a Sheet

Purpose: Extract all comments from a Google Doc and summarize them in a Google Sheet for review.

function summarizeDocsComments() {

const docId = ‘YOUR_DOCUMENT_ID’;

const doc = DocumentApp.openById(docId);

const comments = Drive.Comments.list(docId);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Doc Comments’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Doc Comments’);

sheet.clear();

sheet.appendRow([‘Comment’, ‘Author’, ‘Date’]);

comments.items.forEach(comment => {

sheet.appendRow([comment.content, comment.author.displayName, comment.createdDate]);

});

}

Explanation: This script collects comments from a specified Google Document, including the comment text, author, and date created, and lists them in a “Doc Comments” sheet for easy review and action.

Optimize Google Drive Storage by Identifying Large Files

Purpose: Identify and list large files in Google Drive to help with storage management.

function identifyLargeFiles() {

const files = DriveApp.getFiles();

const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Large Files Report’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Large Files Report’);

const thresholdSize = 10 * 1024 * 1024; // 10 MB

reportSheet.clear();

reportSheet.appendRow([‘File Name’, ‘Size (MB)’, ‘URL’]);

while (files.hasNext()) {

const file = files.next();

const size = file.getSize() / 1024 / 1024;

if (size > thresholdSize) {

reportSheet.appendRow([file.getName(), size.toFixed(2), file.getUrl()]);

}

}

}

Explanation: This script scans all files in Google Drive, identifying those larger than 10 MB, and lists their name, size in MB, and URL in a “Large Files Report” sheet, aiding in the optimization of storage space.

Custom Email Alerts for Spreadsheet Updates

Purpose: Send custom email alerts when specific columns in a Google Sheet are updated.

function onEdit(e) {

const watchedColumn = 3; // For example, column C

const range = e.range;

const sheet = range.getSheet();

if (range.getColumn() === watchedColumn && sheet.getName() === “Sales Data”) {

const newValue = e.value;

const row = range.getRow();

const item = sheet.getRange(row, 1).getValue(); // Assuming item name is in column A

MailApp.sendEmail(“your-email@example.com”, “Update Notification”, `The item “${item}” has a new value: ${newValue} in the Sales Data sheet.`);

}

}

Explanation: This script triggers when edits are made to the third column (C) of the “Sales Data” sheet. It sends an email notification detailing the change, including the item name from column A and the new value.

Automate File Cleanup in Google Drive

Purpose: Periodically delete files from a specific Google Drive folder that are older than a certain date.

function cleanupOldFiles() {

const folder = DriveApp.getFolderById(“FOLDER_ID”);

const files = folder.getFiles();

const cutoffDate = new Date(new Date().setDate(new Date().getDate() – 30)); // 30 days ago

while (files.hasNext()) {

const file = files.next();

if (file.getDateCreated() < cutoffDate) {

file.setTrashed(true);

}

}

}

Explanation: This script moves files created more than 30 days ago to the trash within the specified folder in Google Drive. Replace “FOLDER_ID” with your actual folder ID.

Sync Google Sheets Rows to Google Calendar Events

Purpose: Create and update Google Calendar events based on the rows in a Google Sheet.

function syncSheetToCalendarEvents() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Event Schedule”);

const rows = sheet.getDataRange().getValues();

const calendar = CalendarApp.getDefaultCalendar();

rows.slice(1).forEach(row => {

const [title, startDate, endDate, eventId] = row;

let event;

if (eventId) {

try {

event = calendar.getEventById(eventId);

event.setTitle(title).setTime(new Date(startDate), new Date(endDate));

} catch(e) {

// Event not found, create a new one

event = calendar.createEvent(title, new Date(startDate), new Date(endDate));

sheet.getRange(row.rowIndex + 1, 4).setValue(event.getId()); // Update the event ID in the sheet

}

} else {

event = calendar.createEvent(title, new Date(startDate), new Date(endDate));

sheet.getRange(row.rowIndex + 1, 4).setValue(event.getId()); // Store the event ID in the sheet

}

});

}

Explanation: This script either updates existing calendar events or creates new ones based on the data in the “Event Schedule” sheet. It assumes columns for the event title, start date, end date, and an optional event ID for tracking.

Generate Invoice PDFs from Google Sheets Data

Purpose: Create PDF invoices from each row in a Google Sheet, using a Google Docs template.

function generateInvoices() {

const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Invoices”);

const templateId = “TEMPLATE_DOC_ID”;

const invoiceData = dataSheet.getDataRange().getValues();

const targetFolder = DriveApp.getFolderById(“TARGET_FOLDER_ID”);

invoiceData.slice(1).forEach(row => {

const [customerName, amountDue, dueDate] = row;

const docCopy = DriveApp.getFileById(templateId).makeCopy(`Invoice for ${customerName}`);

const doc = DocumentApp.openById(docCopy.getId());

const body = doc.getBody();

body.replaceText(“{{CustomerName}}”, customerName);

body.replaceText(“{{AmountDue}}”, amountDue.toString());

body.replaceText(“{{DueDate}}”, dueDate);

doc.saveAndClose();

const pdf = DriveApp.createFile(docCopy.getAs(“application/pdf”));

targetFolder.createFile(pdf);

// Optionally, delete the doc copy

docCopy.setTrashed(true);

});

}

Explanation: This script generates PDF invoices using a Google Docs template for each row in the “Invoices” sheet. It replaces placeholders in the template with actual data (customer name, amount due, due date) and saves the PDFs in a specified Drive folder.

Batch Resize Images in Google Drive

Purpose: Resize all images in a specified Google Drive folder to a maximum width, maintaining aspect ratio.

function resizeImages() {

const folder = DriveApp.getFolderById(“FOLDER_ID”);

const images = folder.getFilesByType(MimeType.JPEG);

while (images.hasNext()) {

const image = images.next();

const blob = image.getBlob();

const img = ImagesService.newImage(blob);

const width = img.getWidth();

const height = img.getHeight();

const maxWidth = 800; // Max width for the image

if (width > maxWidth) {

const newHeight = Math.floor((maxWidth / width) * height);

const resizedImage = img.resize(maxWidth, newHeight).getBlob();

folder.createFile(resizedImage).setName(`resized-${image.getName()}`);

image.setTrashed(true); // Optionally delete the original image

}

}

}

Explanation: This script iterates through JPEG images in a specified folder, resizes images exceeding a maximum width (800 pixels in this case), and maintains their aspect ratio. Resized images are saved with a new name, and original images can be optionally deleted.

Aggregate and Report Task Completion in Google Sheets

Purpose: Summarize completed tasks from a “Tasks” sheet into a monthly report sheet in Google Sheets.

function generateMonthlyTaskReport() {

const tasksSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Tasks”);

const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Monthly Report”) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(“Monthly Report”);

const tasks = tasksSheet.getDataRange().getValues();

const completedTasks = tasks.filter(row => row[2] === “Yes”); // Assuming the completion status is in column C

reportSheet.clear();

reportSheet.appendRow([“Task Name”, “Completion Date”]);

completedTasks.forEach(task => {

reportSheet.appendRow([task[0], task[1]]); // Assuming task name is in column A and completion date is in column B

});

}

Explanation: This script filters for completed tasks in a “Tasks” sheet and generates a summary report in a “Monthly Report” sheet, listing the names and completion dates of completed tasks.

Automate Document Review Reminders

Purpose: Send email reminders for documents in a Google Drive folder that haven’t been updated recently.

function sendDocumentReviewReminders() {

const folder = DriveApp.getFolderById(“FOLDER_ID”);

const files = folder.getFiles();

const reminderThreshold = 30; // days without updates

const today = new Date();

while (files.hasNext()) {

const file = files.next();

const lastUpdated = new Date(file.getLastUpdated());

const daysSinceUpdate = (today – lastUpdated) / (1000 * 60 * 60 * 24);

if (daysSinceUpdate > reminderThreshold) {

MailApp.sendEmail(“your-email@example.com”, “Document Review Reminder”, `The document “${file.getName()}” has not been updated for over ${reminderThreshold} days.`);

}

}

}

Explanation: This script checks for files in a specified folder that haven’t been updated in over 30 days and sends an email reminder for each such document.

Sync Google Sheets Data with External API

Purpose: Periodically fetch and update a Google Sheet with data from an external API.

function syncDataWithExternalAPI() {

const apiURL = “https://api.example.com/data”;

const response = UrlFetchApp.fetch(apiURL);

const data = JSON.parse(response.getContentText());

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“External Data”);

sheet.clear();

sheet.appendRow([“ID”, “Name”, “Value”]); // Assuming these headers match the data structure

data.forEach(item => {

sheet.appendRow([item.id, item.name, item.value]);

});

}

Explanation: This script fetches data from an external API and populates a “External Data” sheet with the received information, replacing any existing content in the sheet.

Implement Feedback Form in Google Docs

Purpose: Create an interactive feedback form within a Google Doc that collects and summarizes responses in a Google Sheet.

function createFeedbackFormInDoc() {

const doc = DocumentApp.getActiveDocument();

const body = doc.getBody();

const formUrl = “https://docs.google.com/forms/d/e/YOUR_FORM_ID/viewform”;

body.appendParagraph(“Please provide your feedback using the form below:”);

body.appendParagraph(formUrl);

}

Explanation: This simple script inserts a link to a Google Forms feedback form within an active Google Doc, guiding users to provide feedback via the form. Responses can be automatically collected and summarized in a linked Google Sheet.

Automate Project Status Reporting

Purpose: Generate a weekly project status report in Google Docs based on data from a “Projects” sheet.

function generateWeeklyProjectStatusReport() {

const projectsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Projects”);

const projects = projectsSheet.getDataRange().getValues();

const doc = DocumentApp.create(‘Weekly Project Status Report’);

const body = doc.getBody();

body.appendParagraph(‘Weekly Project Status Report’).setHeading(DocumentApp.ParagraphHeading.HEADING1);

projects.slice(1).forEach(([projectName, status, dueDate], index) => {

body.appendParagraph(`Project Name: ${projectName}`);

body.appendParagraph(`Status: ${status}`);

body.appendParagraph(`Due Date: ${dueDate}`);

body.appendHorizontalRule();

});

Logger.log(‘Report created: ‘ + doc.getUrl());

}

Explanation: This script creates a new Google Docs document titled “Weekly Project Status Report” and fills it with the project name, current status, and due date for each project listed in a “Projects” sheet.

Track and Visualize Google Forms Survey Responses in Real-Time

Purpose: Create a real-time dashboard in Google Sheets to visualize and track responses from a Google Forms survey.

function createSurveyResponseDashboard() {

const form = FormApp.openById(‘YOUR_FORM_ID’);

const formResponses = form.getResponses();

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Survey Dashboard’);

sheet.clear(); // Prepare the sheet for new data

sheet.appendRow([‘Question’, ‘Answer’, ‘Count’]);

const questionTitles = form.getItems().map(item => item.getTitle());

const responseData = {};

// Initialize responseData object

questionTitles.forEach(title => {

responseData[title] = {};

});

// Aggregate responses

formResponses.forEach(response => {

const itemResponses = response.getItemResponses();

itemResponses.forEach(itemResponse => {

const question = itemResponse.getItem().getTitle();

const answer = itemResponse.getResponse();

responseData[question][answer] = (responseData[question][answer] || 0) + 1;

});

});

// Write aggregated data to the sheet

Object.keys(responseData).forEach(question => {

Object.keys(responseData[question]).forEach(answer => {

sheet.appendRow([question, answer, responseData[question][answer]]);

});

});

}

Explanation: This script aggregates responses from a specified Google Form, counting how many times each answer was selected for each question, and displays this data in a “Survey Dashboard” sheet, providing a simple form of real-time response analysis.

Auto-Generate Custom Google Calendar Invites from Sheets

Purpose: Send customized Google Calendar invites based on a schedule and participant list maintained in a Google Sheet.

function sendCustomCalendarInvites() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Event Schedule’);

const events = sheet.getDataRange().getValues().slice(1); // Skip header row

const calendar = CalendarApp.getDefaultCalendar();

events.forEach(([title, description, startDate, endDate, attendees]) => {

const event = calendar.createEvent(title, new Date(startDate), new Date(endDate), {description: description});

const attendeeEmails = attendees.split(‘;’);

attendeeEmails.forEach(email => event.addGuest(email));

});

}

Explanation: This script reads event details from an “Event Schedule” sheet, including a semicolon-separated list of attendees, and creates Google Calendar events with custom invitations sent to all listed attendees.

Monitor and Report File Activity in Shared Drive Folders

Purpose: Generate a report on recent file activities within a specified Google Drive folder, including creation, modification, and deletion events.

function reportDriveFolderActivity() {

const folderId = ‘YOUR_FOLDER_ID’;

const folder = DriveApp.getFolderById(folderId);

const files = folder.getFiles();

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Folder Activity Report’);

sheet.clear();

sheet.appendRow([‘File Name’, ‘Action’, ‘Timestamp’]);

while (files.hasNext()) {

const file = files.next();

// For simplicity, assuming all files are new or modified recently.

// A real script might compare timestamps or use PropertiesService to track state.

sheet.appendRow([file.getName(), ‘Modified/Added’, file.getLastUpdated()]);

}

// Note: Detecting deletions requires storing and comparing state over time, which is more complex.

}

Explanation: This simplified script logs the names and last modified timestamps of files in a specified Google Drive folder to a “Folder Activity Report” sheet, aimed at tracking recent modifications or additions. True deletion tracking would require more sophisticated state management.

Automate Feedback Collection and Analysis from Google Docs Comments

Purpose: Collect all comments from multiple Google Docs within a folder, analyze sentiment, and report findings in a Google Sheet.

function collectAndAnalyzeDocComments() {

const folderId = ‘YOUR_FOLDER_ID’;

const folder = DriveApp.getFolderById(folderId);

const docs = folder.getFilesByType(MimeType.GOOGLE_DOCS);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Docs Feedback Analysis’);

sheet.clear();

sheet.appendRow([‘Document Name’, ‘Comment’, ‘Sentiment Score’]);

while (docs.hasNext()) {

const docFile = docs.next();

const doc = DocumentApp.openById(docFile.getId());

const docName = doc.getName();

const comments = Docs.Comments.list(docFile.getId()).items;

comments.forEach(comment => {

const sentimentScore = analyzeSentiment(comment.content); // Placeholder function

sheet.appendRow([docName, comment.content, sentimentScore]);

});

}

}

// Placeholder for sentiment analysis function

function analyzeSentiment(text) {

// Implement call to a sentiment analysis API or use a built-in library

return Math.random() * 2 – 1; // Mock sentiment score between -1 and 1

}

Explanation: This script iterates through Google Docs in a specified folder, collects comments from each document, and uses a hypothetical analyzeSentiment function to assign a sentiment score to each comment. The document name, comment text, and sentiment score are then summarized in a “Docs Feedback Analysis” sheet.

Dynamic Project Timeline Visualization in Sheets

Purpose: Create a dynamic project timeline in Google Sheets, automatically adjusting based on project start and end dates.

function createProjectTimeline() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Project Timeline’);

const projects = sheet.getDataRange().getValues().slice(1); // Skip header row

const timelineSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Timeline Visualization’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Timeline Visualization’);

timelineSheet.clear();

const today = new Date();

const dateFormat = timelineSheet.getRange(‘A1’).setNumberFormat(‘MMM d, yyyy’);

projects.forEach((project, index) => {

const [projectName, startDate, endDate] = project;

const startRow = 2 + index * 2; // Spacing between projects for readability

const duration = (new Date(endDate) – new Date(startDate)) / (24 * 3600 * 1000); // Duration in days

const startOffset = (new Date(startDate) – today) / (24 * 3600 * 1000); // Start offset from today

// Set project name

timelineSheet.getRange(startRow, 1).setValue(projectName);

// Visualize timeline as filled cells

for (let i = 0; i <= duration; i++) {

timelineSheet.getRange(startRow + 1, 2 + startOffset + i).setBackground(‘blue’).setBorder(null, null, null, null, null, null, ‘white’, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

});

}

Explanation: This script reads project names, start dates, and end dates from a “Project Timeline” sheet, then visualizes this timeline in a separate “Timeline Visualization” sheet. Each project’s duration is represented by filled cells, creating a simple Gantt-like chart.

Analyze Text for Keywords and Generate Summary Report

Purpose: Extract and count occurrences of specified keywords within text documents in Google Drive, reporting the findings in a Google Sheet.

function analyzeTextForKeywords() {

const keywords = [‘project’, ‘deadline’, ‘budget’];

const folderId = ‘YOUR_FOLDER_ID’;

const folder = DriveApp.getFolderById(folderId);

const files = folder.getFilesByType(MimeType.GOOGLE_DOCS);

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Keyword Analysis’);

sheet.clear();

sheet.appendRow([‘Document Name’, …keywords, ‘Total Count’]);

while (files.hasNext()) {

const file = files.next();

const doc = DocumentApp.openById(file.getId());

const text = doc.getBody().getText().toLowerCase();

const counts = keywords.map(keyword => (text.match(new RegExp(keyword, ‘g’)) || []).length);

const totalCount = counts.reduce((a, b) => a + b, 0);

sheet.appendRow([doc.getName(), …counts, totalCount]);

}

}

Explanation: This script searches through text documents in a specified Google Drive folder for predefined keywords, counts how many times each keyword appears, and compiles a report in a “Keyword Analysis” sheet, including the total count of all keywords per document.

Generate Personalized Certificates from Google Sheets

Purpose: Create personalized completion certificates for participants listed in a Google Sheet using a Google Slides template.

function generateCertificates() {

const templateId = ‘YOUR_TEMPLATE_ID’;

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Participants’);

const participants = sheet.getDataRange().getValues().slice(1); // Skip header row

const targetFolder = DriveApp.getFolderById(‘TARGET_FOLDER_ID’);

participants.forEach(([name, course]) => {

const presentation = SlidesApp.openById(templateId).copy(`${name} – ${course} Certificate`);

const slide = presentation.getSlides()[0];

slide.replaceAllText(‘{{Name}}’, name);

slide.replaceAllText(‘{{Course}}’, course);

const blob = presentation.getBlob();

targetFolder.createFile(blob).setName(`${name} – ${course} Certificate.pdf`);

DriveApp.getFileById(presentation.getId()).setTrashed(true); // Clean up by deleting the temporary copy

});

}

Explanation: This script generates personalized PDF certificates for each participant listed in a “Participants” sheet, filling in their name and course on a Google Slides template, and saves the PDFs in a specified Drive folder. The script cleans up by deleting the temporary Slides copy after conversion.

Auto-Generate Monthly Budget Reports in Sheets

Purpose: Summarize and report monthly expenses and income in a Google Sheet, categorizing data and calculating net savings.

function generateMonthlyBudgetReport() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Transactions’);

const transactions = sheet.getDataRange().getValues().slice(1); // Skip header row

const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Monthly Budget Report’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Monthly Budget Report’);

const categories = […new Set(transactions.map(transaction => transaction[2]))]; // Extract unique categories

const summary = {};

// Initialize summary object

categories.forEach(category => {

summary[category] = { income: 0, expenses: 0 };

});

// Aggregate transactions

transactions.forEach(([date, amount, category]) => {

if (amount > 0) {

summary[category].income += amount;

} else {

summary[category].expenses += Math.abs(amount);

}

});

// Write summary to report sheet

reportSheet.clear();

reportSheet.appendRow([‘Category’, ‘Income’, ‘Expenses’, ‘Net’]);

Object.keys(summary).forEach(category => {

const { income, expenses } = summary[category];

const net = income – expenses;

reportSheet.appendRow([category, income, expenses, net]);

});

}

Explanation: This script categorizes and summarizes income and expenses from a “Transactions” sheet, calculating net savings for each category, and compiles a detailed “Monthly Budget Report” sheet, providing insights into financial health.

Automate Outreach Emails Based on Sheets Data

Purpose: Send customized outreach emails to a list of contacts in a Google Sheet, tracking sent messages.

function sendOutreachEmails() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Outreach Contacts’);

const contacts = sheet.getDataRange().getValues().slice(1); // Skip header row

const sentColumnIndex = 4; // Assuming the ‘Email Sent’ status is in column E

contacts.forEach((row, index) => {

const [name, email, message, sentStatus] = row;

if (sentStatus !== ‘Yes’) {

const personalizedMessage = `Dear ${name},\n\n${message}`;

MailApp.sendEmail(email, ‘Outreach Subject’, personalizedMessage);

sheet.getRange(index + 2, sentColumnIndex + 1).setValue(‘Yes’); // Mark as sent

}

});

}

Explanation: This script iterates through a list of contacts in an “Outreach Contacts” sheet, sends a personalized email to each contact who hasn’t already been contacted (as indicated by an ‘Email Sent’ status), and updates the sheet to reflect that the email has been sent.

Create a Task Management System in Google Sheets

Purpose: Implement a simple task management system within Google Sheets, allowing for task addition, completion tracking, and automatic prioritization.

function manageTasks() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Tasks’);

const tasks = sheet.getDataRange().getValues().slice(1); // Skip header row

const sortedTasks = tasks.sort((a, b) => b[2] – a[2]); // Sort by priority, assuming priority is in column C

// Clear existing tasks and re-write sorted tasks

sheet.clearContents().appendRow([‘Task’, ‘Status’, ‘Priority’]);

sortedTasks.forEach(task => sheet.appendRow(task));

// Example task addition (this could be adapted to take input from a form or UI)

sheet.appendRow([‘New Task’, ‘Pending’, 5]); // Add a new task with a priority

}

Explanation: This script creates a basic task management system within a “Tasks” sheet, sorting tasks by priority and allowing for the addition of new tasks. Tasks can be marked as completed manually, or additional scripting could automate status updates based on other criteria.

Automatically Translate Spreadsheet Content

Purpose: Translate the content of a specified column in a Google Sheet into another language using Google Translate and write the translations to another column.

function autoTranslateSheetContent() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Translations’);

const range = sheet.getDataRange();

const values = range.getValues();

const sourceColumnIndex = 1; // Assuming source text is in column B

const targetColumnIndex = 2; // Place translations in column C

const targetLanguage = ‘es’; // Translate to Spanish

values.forEach((row, i) => {

const sourceText = row[sourceColumnIndex – 1];

if (sourceText) {

const translatedText = LanguageApp.translate(sourceText, ”, targetLanguage);

sheet.getRange(i + 1, targetColumnIndex).setValue(translatedText);

}

});

}

Explanation: This script translates text from one column of a Google Sheet into Spanish, placing the translated text into another column. It uses Google’s built-in LanguageApp for translation.

Generate Email Drafts from Spreadsheet Data

Purpose: Create email drafts based on data from a Google Sheet, using each row’s information to customize the email content.

function generateEmailDrafts() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Emails’);

const emails = sheet.getDataRange().getValues().slice(1); // Skip header

emails.forEach(([recipient, subject, body]) => {

GmailApp.createDraft(recipient, subject, body);

});

}

Explanation: This script reads recipient email addresses, subjects, and body texts from a “Emails” sheet to create personalized email drafts in Gmail for each row.

Automate Resource Booking Confirmation Emails

Purpose: Send confirmation emails automatically when a booking is made in a Google Sheet, including details about the resource booked and the booking time.

function sendBookingConfirmationEmails() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Bookings’);

const bookings = sheet.getDataRange().getValues().slice(1); // Assuming headers

bookings.forEach(([name, email, resource, date]) => {

if (email) { // Check if email column is not empty

const message = `Hello ${name},\n\nYour booking for ${resource} on ${date} has been confirmed.`;

const subject = `Booking Confirmation for ${resource}`;

MailApp.sendEmail(email, subject, message);

}

});

}

Explanation: This script sends personalized booking confirmation emails for entries in a “Bookings” sheet, ensuring users are notified upon their booking being registered.

Sync Google Calendar with Sheet Events Automatically

Purpose: Automatically synchronize events from a Google Sheet to a Google Calendar, creating new events and updating or removing existing ones based on the sheet data.

function syncSheetEventsWithCalendar() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Events’);

const events = sheet.getDataRange().getValues().slice(1); // Skip header

const calendar = CalendarApp.getDefaultCalendar();

events.forEach(([title, description, startTime, endTime, eventId]) => {

let event;

if (eventId) {

// Attempt to update existing event

try {

event = calendar.getEventById(eventId);

event.setTitle(title).setDescription(description).setTime(new Date(startTime), new Date(endTime));

} catch (e) {

// If event does not exist, create a new one

event = calendar.createEvent(title, new Date(startTime), new Date(endTime), {description: description});

sheet.getRange(events.indexOf([title, description, startTime, endTime, eventId]) + 2, 5).setValue(event.getId());

}

} else {

// Create new event if no ID exists

event = calendar.createEvent(title, new Date(startTime), new Date(endTime), {description: description});

sheet.getRange(events.indexOf([title, description, startTime, endTime, eventId]) + 2, 5).setValue(event.getId());

}

});

}

Explanation: This script maintains synchronization between a Google Sheet and Calendar. It updates existing events if they’re modified in the sheet, creates new events if they don’t exist, and keeps track of event IDs in the sheet for reference.

Monitor and Alert for Spreadsheet Anomalies

Purpose: Watch for anomalies in a Google Sheet’s data, such as values falling outside expected ranges, and send an alert email if any are found.

function monitorSpreadsheetForAnomalies() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data Monitoring’);

const range = sheet.getDataRange();

const values = range.getValues();

let anomalies = [];

values.forEach((row, rowIndex) => {

// Assuming the value to monitor is in column A (index 0)

const value = row[0];

if (value < 10 || value > 100) { // Example anomaly conditions

anomalies.push(`Row ${rowIndex + 1}: Value ${value}`);

}

});

if (anomalies.length > 0) {

MailApp.sendEmail(‘your-email@example.com’, ‘Spreadsheet Anomaly Alert’, `Anomalies detected:\n\n${anomalies.join(‘\n’)}`);

}

}

Explanation: This script scans a specific column in a “Data Monitoring” sheet for values outside of a defined range (e.g., 10 to 100) and compiles a list of anomalies. If any anomalies are detected, it sends an alert email with the details.

Create a Dynamic FAQ Document from Sheet Data

Purpose: Generate a Google Docs FAQ document dynamically from a Google Sheet containing questions and answers.

function generateDynamicFaqDocument() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘FAQs’);

const faqs = sheet.getDataRange().getValues().slice(1); // Skip header

const doc = DocumentApp.create(‘FAQ Document’);

const body = doc.getBody();

faqs.forEach(([question, answer], index) => {

body.appendParagraph(`Q${index + 1}: ${question}`).setHeading(DocumentApp.ParagraphHeading.HEADING2);

body.appendParagraph(answer);

body.appendHorizontalRule();

});

}

Explanation: This script reads a list of frequently asked questions (FAQs) and their answers from a “FAQs” sheet and creates a formatted Google Docs document, with each question styled as a heading and followed by its answer.

Implement an Issue Tracking System in Sheets

Purpose: Use Google Sheets to develop a simple issue tracking system, enabling issue logging, status updates, and prioritization.

function createIssueTrackingSystem() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Issue Tracker’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Issue Tracker’);

sheet.clear();

sheet.appendRow([‘Issue ID’, ‘Description’, ‘Status’, ‘Priority’, ‘Created Date’, ‘Resolved Date’]);

// Example of adding a new issue

const newIssue = [‘ISSUE-1’, ‘Example issue description’, ‘Open’, ‘High’, new Date(), ”];

sheet.appendRow(newIssue);

// Example of updating an issue’s status

const issues = sheet.getDataRange().getValues();

const issueToUpdate = ‘ISSUE-1’;

issues.forEach((issue, index) => {

if (issue[0] === issueToUpdate) {

sheet.getRange(index + 1, 3).setValue(‘Closed’); // Update status to Closed

sheet.getRange(index + 1, 6).setValue(new Date()); // Set resolved date

}

});

}

Explanation: This script sets up a basic issue tracking system within a Google Sheet, allowing for the addition of new issues and the updating of issue statuses and resolution dates.

Automatically Archive Old Emails in Gmail

Purpose: Search for and automatically archive emails in Gmail that are older than a specified age, based on a certain label.

function autoArchiveOldEmails() {

const searchQuery = ‘label:inbox older_than:30d’; // Adjust the query as needed

const threads = GmailApp.search(searchQuery);

threads.forEach(thread => {

thread.moveToArchive();

});

}

Explanation: This script finds all emails in the inbox that are older than 30 days and archives them, helping to keep the inbox clean and organized without permanently deleting any emails.

Generate Meeting Notes Template in Google Docs

Purpose: Create a Google Docs template for meeting notes, including sections for attendees, agenda items, and action points, filled out with sample content.

function generateMeetingNotesTemplate() {

const doc = DocumentApp.create(‘Meeting Notes Template’);

const body = doc.getBody();

body.appendParagraph(‘Meeting Notes’).setHeading(DocumentApp.ParagraphHeading.HEADING1);

body.appendParagraph(‘Date: ‘).appendPlaceholder(‘Date’);

body.appendParagraph(‘Attendees: ‘).appendPlaceholder(‘List of attendees’);

body.appendParagraph(‘Agenda:’).setHeading(DocumentApp.ParagraphHeading.HEADING2);

body.appendParagraph(‘1. Agenda Item 1’);

body.appendParagraph(‘2. Agenda Item 2’);

body.appendParagraph(‘Action Items:’).setHeading(DocumentApp.ParagraphHeading.HEADING2);

body.appendParagraph(‘- [ ] Action Item 1’);

body.appendParagraph(‘- [ ] Action Item 2’);

}

Explanation: This script creates a structured template for meeting notes in Google Docs, featuring placeholders and sections for key meeting details, agenda items, and action points, facilitating organized note-taking.

Automate Data Backup from Google Sheets to Google Drive

Purpose: Regularly export and save a backup of a Google Sheet to a specified Google Drive folder in a specific format.

function backupSheetToDrive() {

const sheetId = SpreadsheetApp.getActiveSpreadsheet().getId();

const folderId = ‘YOUR_FOLDER_ID’; // The ID of the folder where backups will be saved

const fileName = `Backup_${new Date().toISOString().slice(0, 10)}`; // Name the file with the current date

const url = `https://docs.google.com/spreadsheets/d/${sheetId}/export?format=xlsx`; // Export as Excel file

const params = {

method: “get”,

headers: {“Authorization”: “Bearer ” + ScriptApp.getOAuthToken()},

muteHttpExceptions: true

};

const response = UrlFetchApp.fetch(url, params);

const blob = response.getBlob().setName(fileName + ‘.xlsx’);

DriveApp.getFolderById(folderId).createFile(blob);

}

Explanation: This script automatically exports the active Google Sheet as an Excel file and saves it to a designated Google Drive folder, providing a way to regularly back up important data. Adjust the export format as needed (e.g., xlsx for Excel, pdf for PDF).

Custom Data Validation Based on External API Call

Purpose: Perform custom data validation in a Google Sheet based on responses from an external API, marking invalid entries in a separate column.

function validateDataWithExternalAPI() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data Validation’);

const dataRange = sheet.getDataRange();

const data = dataRange.getValues();

data.forEach((row, index) => {

// Assuming data to validate is in the first column

const validationResponse = UrlFetchApp.fetch(‘https://api.example.com/validate?query=’ + encodeURIComponent(row[0]));

const isValid = JSON.parse(validationResponse.getContentText()).isValid;

// Assuming the validation result should be placed in the second column

sheet.getRange(index + 1, 2).setValue(isValid ? ‘Valid’ : ‘Invalid’);

});

}

Explanation: This script checks each entry in the first column of a “Data Validation” sheet against an external validation API. It then writes the validation result (Valid or Invalid) in the adjacent cell in the second column.

Automatically Organize Google Drive Files by Type

Purpose: Sort files in your Google Drive into folders based on their MIME type.

function organizeDriveFilesByType() {

const files = DriveApp.getFiles();

const typeFolders = {};

while (files.hasNext()) {

const file = files.next();

const mimeType = file.getMimeType();

if (!typeFolders[mimeType]) {

// Create or fetch existing folder for this type

let folders = DriveApp.getFoldersByName(mimeType);

typeFolders[mimeType] = folders.hasNext() ? folders.next() : DriveApp.createFolder(mimeType);

}

// Move file into its respective type folder

typeFolders[mimeType].addFile(file);

}

}

Explanation: This script iterates through all files in the user’s Google Drive and moves them into folders named after their MIME type, effectively organizing the files based on their format.

Synchronize Google Contacts with a Spreadsheet

Purpose: Keep Google Contacts synchronized with contact information maintained in a Google Sheet, updating existing contacts and adding new ones as needed.

function syncContactsFromSheet() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Contacts’);

const contacts = sheet.getDataRange().getValues().slice(1); // Skip header row

const existingContacts = ContactsApp.getContacts();

contacts.forEach(([name, email, phone]) => {

const matchedContacts = existingContacts.filter(contact => 

contact.getFullName() === name || contact.getEmails().some(emailField => emailField.getAddress() === email)

);

if (matchedContacts.length === 0) {

// Create new contact

const newContact = ContactsApp.createContact(name, ”, email);

newContact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone);

} else {

// Update existing contact

const contact = matchedContacts[0];

contact.setEmails([]);

contact.addEmail(ContactsApp.Field.WORK_EMAIL, email);

contact.setPhones([]);

contact.addPhone(ContactsApp.Field.MOBILE_PHONE, phone);

}

});

}

Explanation: This script updates Google Contacts based on a “Contacts” sheet. For each row, it checks if a contact with the given name or email already exists. If so, it updates the contact’s information; if not, it creates a new contact.

Batch Process Images in Google Drive

Purpose: Resize and convert images stored in a specific Google Drive folder to a different format, saving the processed images in another folder.

function batchProcessImages() {

const sourceFolder = DriveApp.getFolderById(‘SOURCE_FOLDER_ID’);

const targetFolder = DriveApp.getFolderById(‘TARGET_FOLDER_ID’);

const images = sourceFolder.getFilesByType(MimeType.JPEG);

while (images.hasNext()) {

const image = images.next();

const imageBlob = image.getBlob();

const resizedImageBlob = ImagesService.newImage(imageBlob).resize(800, 600).getBlob().getAs(MimeType.PNG);

targetFolder.createFile(resizedImageBlob.setName(image.getName().replace(/\.[^/.]+$/, “”) + “.png”));

}

}

Explanation: This script resizes JPEG images found in a specified source folder to 800×600 pixels and converts them to PNG format. The processed images are then saved in a target folder, demonstrating batch image processing.

Automate Document Version Control

Purpose: Create a version control system for Google Docs that saves a timestamped copy of the document in a designated Google Drive folder each time it’s edited.

function automateDocumentVersionControl() {

const doc = DocumentApp.getActiveDocument();

const targetFolder = DriveApp.getFolderById(‘VERSION_CONTROL_FOLDER_ID’);

const timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), “yyyyMMddHHmmss”);

const versionedFileName = doc.getName() + “_version_” + timestamp;

DriveApp.getFileById(doc.getId()).makeCopy(versionedFileName, targetFolder);

}

Explanation: This script can be attached to a document’s onEdit trigger (though Google Docs doesn’t support simple triggers like onEdit, this would conceptually be how it works). It saves a new, timestamped copy of the document to a version control folder each time a change is made, allowing you to maintain historical versions.

Generate and Email Custom Reports from Sheets

Purpose: Compile data from a Google Sheet into a custom report and email it as a PDF attachment.

function generateAndEmailCustomReports() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Monthly Sales’);

const emailAddress = ‘recipient@example.com’;

const subject = ‘Monthly Sales Report’;

const body = ‘Please find attached the Monthly Sales Report.’;

const pdfOptions = ‘exportFormat=pdf&format=pdf&size=letter&portrait=true&fitw=true&top_margin=0.75&bottom_margin=0.75&left_margin=0.7&right_margin=0.7&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=false&fzr=false’;

const url = ‘https://docs.google.com/spreadsheets/d/’ + SpreadsheetApp.getActiveSpreadsheet().getId() + ‘/export?’ + pdfOptions;

const token = ScriptApp.getOAuthToken();

const response = UrlFetchApp.fetch(url, {

headers: {

‘Authorization’: ‘Bearer ‘ + token

}

});

const blob = response.getBlob().setName(subject + ‘.pdf’);

MailApp.sendEmail({

to: emailAddress,

subject: subject,

body: body,

attachments: [blob]

});

}

Explanation: This script compiles the “Monthly Sales” sheet data into a report, exports it as a PDF, and emails this PDF as an attachment. It demonstrates how to programmatically create, export, and send reports based on Google Sheets data.

Automate Weekly Time-Off Requests Approval

Purpose: Automatically approve or reject time-off requests submitted via a Google Form, based on predefined criteria, and update the Google Sheet with the decision.

function automateTimeOffRequestsApproval() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Time Off Requests’);

const requests = sheet.getDataRange().getValues().slice(1); // Skip header row

requests.forEach((row, i) => {

const [employeeName, startDate, endDate, status] = row;

const rowIndex = i + 2; // Adjust for header row and 1-based indexing

const today = new Date();

const start = new Date(startDate);

const end = new Date(endDate);

if (status !== ‘Approved’ && status !== ‘Rejected’) {

// Example criteria: reject if start date is in the past, otherwise approve

const decision = start < today ? ‘Rejected’ : ‘Approved’;

sheet.getRange(rowIndex, 4).setValue(decision); // Assuming status is in column D

}

});

}

Explanation: This script processes time-off requests stored in a “Time Off Requests” sheet, approving requests whose start date is in the future and rejecting requests with a start date in the past, then updates each row with the decision.

Monitor Sheet Edits for Compliance

Purpose: Log edits made to sensitive columns in a Google Sheet for compliance purposes, including the editor’s email, timestamp, and the old and new values.

function logSheetEditsForCompliance(e) {

const sensitiveColumns = [2, 3]; // Monitor edits in columns B and C for compliance

const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Edit Log’);

if (sensitiveColumns.includes(e.range.getColumn())) {

const userEmail = Session.getActiveUser().getEmail();

const timestamp = new Date();

const oldValue = e.oldValue || ‘None’;

const newValue = e.value || ‘None’;

logSheet.appendRow([userEmail, timestamp, e.range.getA1Notation(), oldValue, newValue]);

}

}

Explanation: Attached to a sheet’s onEdit trigger, this script logs detailed information about edits made to specified sensitive columns in a “Edit Log” sheet, aiding in compliance and auditing processes.

Dynamic Resource Allocation Based on Project Load

Purpose: Dynamically allocate resources in a Google Sheet based on project load, adjusting assignments weekly based on project status and resource availability.

function dynamicResourceAllocation() {

const projectsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Projects’);

const resourcesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Resources’);

const allocationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Allocation’);

const projects = projectsSheet.getDataRange().getValues().slice(1);

const resources = resourcesSheet.getDataRange().getValues().slice(1);

allocationSheet.clear();

allocationSheet.appendRow([‘Project’, ‘Assigned Resource’, ‘Week’]);

projects.forEach(project => {

const [projectName, , projectStatus] = project;

if (projectStatus === ‘Active’) {

resources.forEach(resource => {

const [resourceName, resourceAvailability] = resource;

if (resourceAvailability === ‘Available’) {

// Example simplistic allocation: assign first available resource to active project

allocationSheet.appendRow([projectName, resourceName, ‘This Week’]);

return;

}

});

}

});

}

Explanation: This script assigns available resources to active projects based on data from “Projects” and “Resources” sheets. It populates an “Allocation” sheet with these assignments, providing a simple method for managing weekly resource allocation.

Automatically Clean Up and Organize Google Drive

Purpose: Periodically scan Google Drive for files and folders that haven’t been accessed or modified within a certain timeframe, moving them to an “Archive” folder.

function cleanupAndOrganizeDrive() {

const allFiles = DriveApp.getFiles();

const archiveFolder = DriveApp.getFoldersByName(‘Archive’).next();

const cutoffDate = new Date(new Date().setFullYear(new Date().getFullYear() – 1)); // 1 year ago

while (allFiles.hasNext()) {

const file = allFiles.next();

if (file.getLastUpdated() < cutoffDate) {

archiveFolder.addFile(file);

}

}

}

Explanation: This script helps manage Google Drive storage by identifying files that haven’t been updated in the last year and moving them to an “Archive” folder, thus keeping the Drive organized and making important files more accessible.

Automated Invoice Generation from Timesheet Entries

Purpose: Generate invoices in Google Docs for clients based on timesheet entries stored in a Google Sheet, including detailed work logs and total hours billed.

function generateInvoicesFromTimesheets() {

const timesheetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Timesheets’);

const timesheetData = timesheetSheet.getDataRange().getValues();

const clientInfoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Client Info’);

const clientData = clientInfoSheet.getDataRange().getValues();

const invoiceTemplateId = ‘TEMPLATE_DOC_ID’;

clientData.slice(1).forEach(([clientId, clientName, clientEmail]) => {

const clientTimesheets = timesheetData.filter(row => row[1] === clientId);

if (clientTimesheets.length > 0) {

const docCopy = DriveApp.getFileById(invoiceTemplateId).makeCopy(`Invoice for ${clientName}`);

const doc = DocumentApp.openById(docCopy.getId());

const body = doc.getBody();

body.replaceText(‘{{ClientName}}’, clientName);

let totalHours = 0;

clientTimesheets.forEach(([date, , project, hours]) => {

totalHours += hours;

body.appendParagraph(`${date}: ${project} – ${hours} hours`);

});

body.replaceText(‘{{TotalHours}}’, totalHours.toString());

body.replaceText(‘{{InvoiceDate}}’, Utilities.formatDate(new Date(), Session.getScriptTimeZone(), “yyyy-MM-dd”));

// Additional steps could include emailing the invoice to the client or saving it to a specific Drive folder

}

});

}

Explanation: This script matches timesheet entries for each client stored in a “Timesheets” sheet against client information in a “Client Info” sheet. It then creates personalized invoices based on a Google Docs template for each client with billable hours, dynamically filling in client names, dates, project details, total hours, and the current invoice date.

Dynamic Resource Allocation Spreadsheet

Purpose: Create a spreadsheet that dynamically allocates resources to projects based on priority and resource availability, updating allocations weekly.

function dynamicResourceAllocation() {

const projectsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Projects’);

const resourcesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Resources’);

const allocationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Resource Allocation’);

const projects = projectsSheet.getDataRange().getValues().slice(1); // Skip header

const resources = resourcesSheet.getDataRange().getValues().slice(1); // Skip header

allocationSheet.clear();

allocationSheet.appendRow([‘Week’, ‘Project’, ‘Resource’, ‘Hours Allocated’]);

projects.forEach(project => {

const [projectName, , projectPriority] = project;

resources.forEach(resource => {

const [resourceName, resourceAvailability] = resource;

if (resourceAvailability.toLowerCase() === ‘available’) {

const hoursAllocated = projectPriority * 5; // Simplified allocation logic based on project priority

allocationSheet.appendRow([‘This Week’, projectName, resourceName, hoursAllocated]);

}

});

});

}

Explanation: This script assesses projects and resources listed in separate sheets, then allocates resources to projects in the “Resource Allocation” sheet based on project priority and resource availability, assigning hours in a simplified manner that depends on the project’s priority level.

Custom Email Digest Based on Spreadsheet Updates

Purpose: Compile a weekly email digest summarizing updates made to a project tracking spreadsheet, including new projects added and status changes.

function sendWeeklyEmailDigest() {

const projectSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Project Tracking’);

const lastWeek = new Date();

lastWeek.setDate(lastWeek.getDate() – 7);

const updates = [];

const projects = projectSheet.getDataRange().getValues();

projects.slice(1).forEach(([projectName, status, updatedDate]) => {

const dateUpdated = new Date(updatedDate);

if (dateUpdated >= lastWeek) {

updates.push(`${projectName} is now ${status}`);

}

});

if (updates.length > 0) {

const emailAddress = ‘manager@example.com’;

const subject = ‘Weekly Project Update Digest’;

const body = `Here are the project updates from the last week:\n\n${updates.join(‘\n’)}`;

MailApp.sendEmail(emailAddress, subject, body);

}

}

Explanation: This script checks for changes in a “Project Tracking” sheet, focusing on projects updated in the last week. It compiles a list of these updates and sends a weekly email digest to a specified manager, summarizing new projects added and any status updates.

Automatically Schedule Recurring Google Meet Events

Purpose: Use a Google Sheet to schedule recurring Google Meet events based on specified dates and times, creating calendar events with video conferencing details.

function scheduleRecurringMeetEvents() {

const scheduleSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Meeting Schedule’);

const schedule = scheduleSheet.getDataRange().getValues().slice(1); // Skip header

const calendar = CalendarApp.getDefaultCalendar();

schedule.forEach(([title, description, startDate, startTime, recurrence]) => {

const startDateTime = new Date(`${startDate} ${startTime}`);

const eventSeries = calendar.createEventSeries(title, startDateTime, new Date(startDateTime.getTime() + 3600000), // 1 hour duration

CalendarApp.newRecurrence().addWeeklyRule().times(recurrence),

{description: description, location: ‘Google Meet’, guests: ‘attendee@example.com’, sendInvites: true});

const event = eventSeries.getEvents()[0];

const meetLink = Calendar.Events.get(calendar.getId(), event.getId()).hangoutLink;

Logger.log(`Meet link for ‘${title}’: ${meetLink}`);

});

}

Explanation: This script reads meeting information from a “Meeting Schedule” sheet, including the recurrence of each meeting, and schedules a series of Google Calendar events with Google Meet conferencing details. It logs the Meet link for the first event of each series, facilitating easy access to virtual meeting spaces.

Automated Expense Tracking and Reporting

Purpose: Track expenses submitted through a Google Form, categorize them, and generate monthly expense reports summarizing spending by category.

function generateMonthlyExpenseReports() {

const expensesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Expenses’);

const expenses = expensesSheet.getDataRange().getValues().slice(1); // Skip header

const reportSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Monthly Report’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Monthly Report’);

const categories = […new Set(expenses.map(expense => expense[2]))]; // Unique categories

const monthlyTotals = {};

categories.forEach(category => monthlyTotals[category] = 0);

expenses.forEach(([date, amount, category]) => {

const expenseDate = new Date(date);

const currentMonth = new Date().getMonth();

if (expenseDate.getMonth() === currentMonth) {

monthlyTotals[category] += parseFloat(amount);

}

});

reportSheet.clear();

reportSheet.appendRow([‘Category’, ‘Total Spent’]);

Object.keys(monthlyTotals).forEach(category => {

reportSheet.appendRow([category, monthlyTotals[category]]);

});

}

Explanation: This script calculates and reports monthly spending by category based on expense entries from a “Expenses” sheet. It creates or updates a “Monthly Report” sheet, listing each category’s total spending, aiding in financial tracking and budgeting.

Optimize Team Workload Distribution

Purpose: Analyze task assignments and workloads in a Google Sheet, suggesting optimizations to ensure a balanced distribution of tasks across team members.

function optimizeWorkloadDistribution() {

const tasksSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Tasks’);

const tasks = tasksSheet.getDataRange().getValues().slice(1); // Skip header

const teamSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Team’);

const teamMembers = teamSheet.getDataRange().getValues().slice(1); // Skip header

const workload = {};

teamMembers.forEach(member => workload[member[0]] = 0); // Initialize workload

tasks.forEach(([task, assignee, hours]) => {

workload[assignee] += parseInt(hours, 10);

});

const avgWorkload = Object.values(workload).reduce((a, b) => a + b, 0) / teamMembers.length;

Logger.log(‘Recommended Workload Adjustments:’);

Object.keys(workload).forEach(member => {

if (workload[member] > avgWorkload) {

Logger.log(`${member} is overburdened by ${(workload[member] – avgWorkload).toFixed(2)} hours. Consider reassigning tasks.`);

} else if (workload[member] < avgWorkload) {

Logger.log(`${member} can take on ${(avgWorkload – workload[member]).toFixed(2)} more hours of tasks.`);

}

});

}

Explanation: This script evaluates the distribution of task hours among team members based on a “Tasks” sheet. It calculates each member’s total workload and compares it

Automated Data Cleanup in Sheets with Regular Expressions

Purpose: Clean specific columns in a Google Sheet using regular expressions to remove unwanted characters or patterns from data entries.

function cleanDataWithRegex() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data Cleanup’);

const range = sheet.getDataRange();

const values = range.getValues();

const columnIndex = 2; // Assuming data to clean is in column B

values.forEach((row, index) => {

const originalValue = row[columnIndex – 1];

const cleanedValue = originalValue.replace(/[^\w\s]/gi, ”); // Remove all non-word characters

sheet.getRange(index + 1, columnIndex).setValue(cleanedValue);

});

}

Explanation: This script iterates through all rows in a specified column of the “Data Cleanup” sheet, using a regular expression to remove non-word characters from each entry, and updates the cell with the cleaned data.

Dynamic Gantt Chart Creation in Google Sheets

Purpose: Automatically generate a Gantt chart in a Google Sheet for project management, using start and end dates of tasks to visualize the project timeline.

function generateGanttChart() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Projects’);

const tasks = sheet.getDataRange().getValues().slice(1); // Skip header row

const chartSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Gantt Chart’) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(‘Gantt Chart’);

chartSheet.clear(); // Reset the chart sheet

const headers = [‘Task’, ‘Start Date’, ‘End Date’, ‘Duration’, ‘Complete’];

chartSheet.appendRow(headers);

tasks.forEach(task => {

const [taskName, startDate, endDate] = task;

const duration = (new Date(endDate) – new Date(startDate)) / (24 * 3600 * 1000) + 1; // +1 to include the end date

chartSheet.appendRow([taskName, startDate, endDate, duration, 0]); // 0% complete for simplicity

});

// Add instructions here to transform this data into a Gantt chart using Google Sheets’ chart tools

}

Explanation: After processing tasks from the “Projects” sheet, this script populates a “Gantt Chart” sheet with essential data for each task, including calculated durations. You would then manually use Google Sheets’ chart features to visualize this data as a Gantt chart.

Automate Meeting Minutes Formatting in Google Docs

Purpose: Format meeting minutes in a Google Doc, automatically applying styles to headings, action items, and decisions for easy reading.

function formatMeetingMinutes() {

const doc = DocumentApp.getActiveDocument();

const body = doc.getBody();

const text = body.getText();

const paragraphs = body.getParagraphs();

paragraphs.forEach(paragraph => {

if (paragraph.getText().startsWith(‘Heading:’)) {

paragraph.setHeading(DocumentApp.ParagraphHeading.HEADING1);

} else if (paragraph.getText().startsWith(‘Action Item:’)) {

paragraph.setHeading(DocumentApp.ParagraphHeading.HEADING2).setForegroundColor(‘#d9534f’);

} else if (paragraph.getText().startsWith(‘Decision:’)) {

paragraph.setHeading(DocumentApp.ParagraphHeading.HEADING2).setForegroundColor(‘#5cb85c’);

}

});

}

Explanation: This script searches through all paragraphs in an active Google Doc, identifying those that start with specific keywords (e.g., “Heading:”, “Action Item:”, “Decision:”) and applies formatting to make the document structured and visually organized.

Sync Sheet Data with External Database via API

Purpose: Synchronize a Google Sheet with data from an external database via an API, updating the sheet with the latest data entries.

function syncWithExternalDatabase() {

const apiUrl = ‘https://api.example.com/data’;

const response = UrlFetchApp.fetch(apiUrl);

const data = JSON.parse(response.getContentText());

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘External Data Sync’);

sheet.clear(); // Prepare for fresh data

sheet.appendRow([‘ID’, ‘Name’, ‘Value’]); // Assuming these headers match the data structure

data.forEach(item => {

sheet.appendRow([item.id, item.name, item.value]);

});

}

Explanation: This script fetches data from an external API and updates a “External Data Sync” sheet with the retrieved entries, ensuring the sheet reflects

Log Changes in Spreadsheet to a Separate Sheet

This exercise involves creating a Google Apps Script to log changes made to a specific Google Sheets document to a separate sheet within the same document. This can be useful for auditing or tracking changes made by different users over time.

function onEdit(e) {

var sheetName = “ChangeLog”; // Name of the sheet to log changes

var range = e.range;

var timestamp = new Date();

var user = Session.getActiveUser().getEmail();

var valueBefore = e.oldValue;

var valueAfter = e.value;

var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

if (!logSheet) {

logSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);

}

logSheet.appendRow([timestamp, user, range.getA1Notation(), valueBefore, valueAfter]);

}

Explanation:

• This script uses the onEdit trigger, which runs automatically whenever someone makes an edit in the spreadsheet.
• It captures the details of the edit, including the timestamp, the user who made the change, the cell range, the value before the edit, and the value after the edit.
• It then appends this information to a sheet named “ChangeLog” (you can change the name as needed). If the sheet doesn’t exist, it creates it.

Import JSON Data into Spreadsheet

This exercise involves creating a Google Apps Script to import JSON data into a Google Sheets spreadsheet. You can use this to fetch data from a web API, for example, and display it in your spreadsheet.

function importJSON() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var url = ‘YOUR_JSON_API_URL_HERE’; // Replace with the actual JSON API URL

var response = UrlFetchApp.fetch(url);

var json = response.getContentText();

var data = JSON.parse(json);

var headers = Object.keys(data[0]);

sheet.clear().appendRow(headers);

data.forEach(function(row) {

var rowData = headers.map(function(header) {

return row[header];

});

sheet.appendRow(rowData);

});

}

Explanation:

• This script defines a function importJSON that fetches JSON data from a specified URL using the UrlFetchApp service.
• It then parses the JSON data into a JavaScript object.
• It extracts the headers (keys) from the first object in the JSON data and adds them as the headers in the spreadsheet.
• Finally, it iterates through the JSON data, extracts the values for each row, and appends them to the spreadsheet.

Parse Email Attachments and Save to Google Drive

This exercise involves creating a Google Apps Script to parse email attachments and save them to Google Drive. You can use this script with Gmail to automatically save email attachments to your Google Drive.

function saveAttachmentsToDrive() {

var threads = GmailApp.getInboxThreads(0, 10); // Get the first 10 threads from the inbox

threads.forEach(function(thread) {

var messages = thread.getMessages();

messages.forEach(function(message) {

var attachments = message.getAttachments();

attachments.forEach(function(attachment) {

var folder = DriveApp.getFolderById(‘YOUR_FOLDER_ID’); // Replace with the target folder’s ID

folder.createFile(attachment);

});

});

});

}

Explanation:

• This script defines a function saveAttachmentsToDrive that fetches the first 10 email threads from the inbox using GmailApp.
• It then iterates through each message in those threads and extracts any attachments using getAttachments.
• For each attachment, it specifies a target folder in Google Drive (replace ‘YOUR_FOLDER_ID’ with the actual folder’s ID) and creates a file in that folder using createFile.

Make sure to set up the necessary permissions and triggers for these scripts to run as needed. Additionally, you might need to adapt these scripts to your specific use case and API endpoints if necessary.