5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do almost anything you could do in Microsoft Excel. But the real power of Google Sheets is the Google Scripting feature that comes with it.

Google Apps Scripting is a background scripting tool that works not only in Google Sheets, but also in Google Docs, Gmail, Google Analytics, and almost every other Google cloud service. It allows you to automate these separate applications and integrate each of these applications with each other.

In this article, you will learn how to get started with Google Apps scripting, create a basic script in Google Sheets to read and write cell data, and learn about the most effective advanced scripting features in Google Sheets

How to Create a Google Apps Script

You can start creating your first Google Apps script from Google Sheets right now.

To do this, select the “Tools” menu and then “Script Editor”.

This opens the script editor window and uses the myfunction () function by default. Here you can create and test your google script.

– /

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform calculations, and output the amount of data to another cell.

The function for getting data from a cell is the getRange () and getValue () functions. You can identify a cell by row and column. So, if you have a value in row 2 and column 1 (column A), the first part of your script would look like this:

function myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var row = 2; var col = 1; var data = sheet.getRange (row, column) .getValue (); }

This stores the value from that cell in a data variable. You can perform a calculation on the data and then write that data to another cell. So the last part of this function will look like this:

var results = data * 100; sheet.getRange (row, column + 1) .setValue (results); }

When you’re done writing your function, select the disk icon to save.

The first time you launch this new Google Sheets Script feature (by clicking the launch icon), you will need to provide authorization to run the script on your Google account.

Allow permissions to continue. After running the script, you will see that it has written the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues ??to load arrays

You can take the concept of performing calculations on the data in your spreadsheet through scripting to the next level by using arrays. If you load a variable into a Google Apps script using getValues, the variable will be an array that can load multiple values ??from the table.

function myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var data = sheet.getDataRange (). getValues ??();

A data variable is a multidimensional array containing all the data from a table. To perform calculations on data, you use a for loop. The for loop counter will run on each row, and the column will remain constant depending on the column you want to retrieve data into.

In our example table, you can perform calculations on three rows of data as follows.

for (var i = 1; i

Save and run this script in the same way as above. You will see that all results are listed in column 2 of your table.

You will notice that the cell and string reference in the array variable is different from the getRange function reference.

data [i] [0] refers to dimensions of an array, where the first dimension is a row and the second is a column. They both start from scratch.

getRange (i + 1, 2) refers to the second row when i = 1 (since row 1 is the header) and 2 is the second column where the results are stored.

Use appendRow to write results

What if you have a spreadsheet where you want to write data to a new row instead of a new column?

This is easily done using the appendRow function. This feature will not affect existing data in the table. It will just add a new line to the existing sheet.

As an example, create a function that will count from 1 to 10 and display a counter in multiples of 2 in the Counter column.

This function might look like this:

function myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); for (var i = 1; i <11; i ++) {var result = i * 2; sheet.appendRow ([i, result]); }}

Here are the results when running this function.

Processing RSS Feeds with URLFetchApp

You can combine the previous Google Sheets script function and URLFetchApp to get an RSS feed from any website and write a row to a spreadsheet for every article recently posted to that website.

Basically, it’s a do-it-yourself method to create a spreadsheet for reading RSS feeds!

The scenario for this is also not too complicated.

function myFunction () {var sheet = SpreadsheetApp.getActiveSheet (); var item, date, title, link, desc; var txt = UrlFetchApp.fetch (“https://www.topsecretwriters.com/rss”) .getContentText (); var doc = Xml.parse (txt, false); title = doc.getElement (). getElement (“channel”). getElement (“title”). getText (); var items = doc.getElement (). getElement (“channel”). getElements (“element”); // Analyze individual items in the RSS feed for (var i in items) {item = items [i]; title = item.getElement (“title”). getText (); link = item.getElement (“link”). getText (); date = item.getElement (“pubDate”). getText (); desc = item.getElement (“description”). getText (); sheet.appendRow ([title, link, date, description]); }}

As you can see, Xml.parse extracts each item from the RSS feed and splits each line into a title, link, date, and description.

Using the appendRow function, you can place these items in the appropriate columns for each individual RSS feed item.

The output on your sheet will look something like this:

Instead of embedding the RSS feed url in the script, you can have a field in your sheet with the url and then have multiple sheets – one for each website you want to track.

Concatenated strings and add a carriage return

You can take your RSS spreadsheet even further by adding some word processing functionality, and then use the email functionality to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, in the script created in the previous section, you need to add a script that will extract all the information from the spreadsheet.

You need to build the subject line and body of the email by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet.

To do this, initialize the subject and message by placing the following lines before the For “items” loop.

var subject = ‘Last 10 articles published on mysite.com’
var message = ”

Then, at the end of the for “items” loop (just after the appendRow function), add the following line.

message = message + title + ‘ n’ + link + ‘ n’ + date + ‘ n’ + desc + ‘ n’ + ‘ n n’;

The “+” character will concatenate all four items together, followed by ” n” for a carriage return after each line. At the end of each block of header data, you will need two carriage returns for a well-formatted email body.

After all lines have been processed, the body variable contains the entire line of the email message. You are now ready to send an email!

How to Send Email in Google Apps Programming

The next section of your google script will be sending the “subject” and “body” by email. It’s very easy to do this in Google Script.

var emailAddress = myemail@gmail.com;
MailApp.sendEmail (emailAddress, subject, message);

MailApp is a very handy class inside Google Apps Scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, a single line with the sendEmail function allows you to send any email with only the email address, subject line and body text.

This is how the received email will look like.

The combination of being able to fetch a website’s RSS feed, save it to a Google spreadsheet, and send it to yourself with URL links included, makes it very convenient to keep an eye on the latest content of any website.

This is just one example of how Google Apps scripts can automate actions and integrate multiple cloud services.

–