Monthly Archives: April 2014

Send Email from Google Sheets using Apps Script

In this post I described how to find the number of rows in a spreadsheet column that contained data. Let’s use that as the basis for a simple script for sending email from Google Sheets.

To send email to the addresses in column 2 – let’s say, to send a newsletter – we need our script to follow a few distinct steps:

  • Calculate how many mails to send
  • Get the range of cells that contains those addresses
  • Get the recipients’ email addresses from those cells
  • Loop through those addresses sending each an email

We’ll be making use of the getRange() method:

getRange(row, column, optNumRows, optNumColumns)

First, the number of emails to send. For this we’ll use the value provided by COUNTA and saved in cell E1, as described in this post. We pass the row and column to getRange(), but without the optional arguments, so it only looks in one cell. I’ve also chained the getValue() method to read the cell contents:

var numRows = sheet.getRange(1, 5).getValue();

Secondly, we need to derive the corresponding range of cells in the sheet. So, in our case, column B starting at row 2, and extending by the number of rows we just saved in numRows:

var startRow = 2; // starting row of data to process
var dataRange = sheet.getRange(startRow, 2, numRows, 1);

...read more

Storing Google Drive Files in More Than One Location

Ever lose a file in Google Drive? In this post I talked about some ways of searching for your files, but there’s another trick you might want to try too.

In this other post I discussed moving a file into an alternative folder. Google Drive folders are not exactly like the folders in the file system of your PC. In fact, they behave more like the labels in Gmail. To make a file easier to find, there’s a simple way to store it in multiple folders and/or subfolders – a little like adding multiple labels to a message stored in Gmail.

...read more

Add a Document Sidebar with Google Apps Script

In this older post I showed you how to use the onOpen() trigger to add a menu item in the old Google Sheets. The interface is a little different in Forms, Docs and the new Google Sheets, so here we’ll look at the new syntax. We’ll then extend this functionality a little by creating a sidebar for a document.

This was how things worked in the old Sheets:

function onOpen() { 
 var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
 var items = [{name:'Show dialog', functionName:'showDialog'}]; 
 sheet.addMenu('Custom Menu', items);
}

In Forms, Docs and the new version of Sheets we instead use the getUi() method of the SpreadsheetApp object (or DocumentApp, FormApp as required) to create and later display the new menu.

Here’s example code for a Document:

function onOpen() {
 DocumentApp.getUi()
     .createMenu('Sidebar').addItem('Open', 'openSidebar').addToUi();
}

...read more