Quick Tutorial: Using Google Sheets Scripts for Workflow Automation

Introduction to Google Apps Script and its applications in workflow automation

Step 1: Identifying the goal and requirements of the workflow

• Our goal is to automatically curate and organize content (e.g., articles, blog posts) from various sources in a Google Sheet for further review and processing.

Step 2: Setting up the Google Sheet

• Create a new Google Sheet and set up column headers to organize the curated content (e.g., title, URL, source, date, etc.).

Step 3: Writing a custom script to fetch content from various sources

• In your Google Sheet, go to “Extensions” > “Apps Script” to open the script editor.
• Write a custom script that uses the “UrlFetchApp” class to fetch content from your desired sources (e.g., websites, RSS feeds, or APIs).
• Parse the fetched content and extract the relevant data (e.g., article titles, URLs) using regular expressions or built-in functions like “JSON.parse()”.
• Append the extracted data to the appropriate columns in your Google Sheet using the “SpreadsheetApp” class.

Example 1: Fetching content from an RSS feed

javascript

function fetchRSSContent() {
// Replace this with the URL of your desired RSS feed
var rssFeedUrl = "https://example.com/rss_feed";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Fetch the content from the RSS feed
var response = UrlFetchApp.fetch(rssFeedUrl);
var xml = XmlService.parse(response.getContentText());
var root = xml.getRootElement();
var entries = root.getChildren("channel")[0].getChildren("item");

// Iterate through each entry in the RSS feed and append the relevant data to the Google Sheet
for (var i = 0; i < entries.length; i++) {
var title = entries[i].getChild("title").getText();
var url = entries[i].getChild("link").getText();
var date = entries[i].getChild("pubDate").getText();
sheet.appendRow([title, url, "RSS Feed", date]);
}
}

Step 4: Setting up script triggers to automate the content curation process

• In the script editor, go to “Triggers” > “Add Trigger” to create a new script trigger.
• Configure the trigger to run your custom script at a specific time interval (e.g., daily or weekly) or based on a specific event (e.g., when the Google Sheet is opened).

javascript

function fetch_content() {
// Fetch content from your desired sources
var url = "https://example.com/api/articles";
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();

// Parse the fetched content and extract relevant data
var data = JSON.parse(content);
var articles = data.articles;

// Append the extracted data to the appropriate columns in your Google Sheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var lastRow = sheet.getLastRow();
for (var i = 0; i < articles.length; i++) {
var article = articles[i];
sheet.getRange(lastRow + i + 1, 1).setValue(article.title);
sheet.getRange(lastRow + i + 1, 2).setValue(article.url);
sheet.getRange(lastRow + i + 1, 3).setValue(article.source);
sheet.getRange(lastRow + i + 1, 4).setValue(article.date);
}
}

// Set up a daily trigger to run the fetch_content function
function set_daily_trigger() {
ScriptApp.newTrigger("fetch_content")
.timeBased()
.everyDays(1)
.create();
}

Step 5: Organizing and processing the curated content using custom functions

• Write additional custom functions in your script to process and organize the curated content as needed (e.g., sorting by date or source, filtering based on keywords or categories).
• Use the “Custom Function” feature in Google Sheets to call your custom functions directly from the cells of your spreadsheet.

Example 2: Sorting content by date

javascript

function sortByDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();

// Sort the data by the date column (column index 3)
data.sort(function(a, b) {
var dateA = new Date(a[3]);
var dateB = new Date(b[3]);
return dateB - dateA;
});

// Write the sorted data back to the Google Sheet
sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}

Example 3: Filtering content based on a keyword

javascript

function filterByKeyword(keyword) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
var filteredData = [];

// Iterate through the data and add rows containing the keyword to the filteredData array
for (var i = 0; i < data.length; i++) {
if (data[i][0].indexOf(keyword) !== -1) {
filteredData.push(data[i]);
}
}

// Create a new sheet and write the filtered data to it
var filteredSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Filtered - " + keyword);
filteredSheet.getRange(1, 1, 1, sheet.getLastColumn()).setValues([sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]]);
filteredSheet.getRange(2, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
}

Step 6: Testing and monitoring the script

• Test your custom script and functions to ensure they are working as intended.
• Monitor the performance of your script triggers and address any issues or errors that may arise.