Skip to content

How to reset a Google Docs spreadsheet by script

Using the Google API to script your spreadsheets (or any other document) is a really common operation but can be difficult for non programmers. In this post you will learn to :

  • Clear a spreadsheet on Google Docs in parts or entirely, by triggering it manually or automatically at a given time.
  • Send email notifications to people so they don’t forget to fill the document again.

I started from a simple problem : at work, we order sandwiches via an Excel sheet on the intranet. The first people who wants to order sandwiches opens it, clears it and sends a reminder to everyone and, later, sends it by email to the sandwiches store. Let’s be honest : it is a real pain in the neck and sometimes, the person who has to send it forgets and nobody gets food. So I decided to automate the process. Of course, it can be used for any kind of order, time shifts, etc. The applications are limitless.

Note that the script will be written in Javascript.

The project requirements

To begin with, you need a Google Docs spreadsheet, for this post, I’ll use this one. Feel free to use your own, I’ll explain how to adapt my code.

spreadsheet_mainI know it’s in French but it doesn’t matter since the sheet’s content is not important for the script, you can replace it by whatever you want.  There are 3 things to notice :

  • There’s a new menu on the top of the page. It’s added by the script and I’ll use it to manually trigger those actions : reset the sheet, send it and send a reminder.
  • I put all the information that doesn’t need to be reset on top of the sheet. It’s not mandatory but it will make your work easier.
  • I actually created 5 sheets. The first one contains the order. The second is static and contains the sandwiches list, you can ignore it. The third one contains an email addresses list. If you want to receive a daily reminder, just put your address in the list, if you want to stop receiving it, just remove your address. The fourth and fifth ones contains templates for the order and the reminder emails so they are easy to edit.

spreadsheet_reminderThe reminder emails list.

spreadsheet_templateThe reminder template.

Step 1 : create a script

Click on the “Tools” menu > “Script manager” and you should see this. Yours will be empty at first, of course.

spreadsheet_scriptmanagerthe script manager

spreadsheet_scripteditor the script editor

When you create a new script, you will be asked to select the script’s type. Select “empty project”. You can also select “spreadsheet project”, it will insert some code samples but you don’t need them.

Google offers you two very useful tools : the logs and the execution transcript. Both can be accessed in the “View” menu.

The logs allow you to log any message or variable value during the script’s execution by simply calling :

Logger.log("some message");

You can replace “some message” by any variable and its text representation will be logged. The execution transcript registers automatically all the function calls with the parameters values. Both are reset at each script execution.

Step 2 : the reset script

// Retrieves a sheet's range from its name
function getRange(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(name);
return sheet.getDataRange();
}

// Resets all the data cells without changing the header
// cells.
function resetSheet() {
// Retrieving the values contained in the sheet.
var range = getRange("Commande");
var values = range.getValues();
// Browsing the rows & cells to reset.
for (var i = COMMAND_ROWS_OFFSET ; i < values.length ; i++) {
for (var j = 0 ; j < values[i].length ; j++) {
// Resetting the cell in the data copy.
values[i][j] = "";
}
}
// Synchronizing the (now empty) data copy and the sheet.
range.setValues(values);
}

A range is the smallest rectangle in a sheet that contains all the data. All the empty rows and columns will be ignored if they aren’t followed by an non-empty row or column. As there is one range per sheet, we also need to know the sheet’s name. You can see it in the sheets list, at the bottom-left of your spreadsheet. But a range is an object, to retrieve the data, you need to call its “getValues” method, it will return the range as an 2-dimensions array. This is not the real data but a copy of the data. So, once you browsed it and cleared all the cells, you’ll still need to replace the range’s values by the copy. It can be done by calling the range’s “setValues” method.

But all the data doesn’t need to be cleared. The sheet’s first lines contain some static information, like who need to receive the command or the user instructions so you won’t browse all the range but skip the first lines.

When computing the number of lines to skip at the top of the sheet, be aware that, if you merge cells, the result is only visual. They will be considered as separated cells in the range but only the first one of the merged cells will contain the data.

Step 3 : the reminder script

// Loads an email template and sends it to the recipients
function sendEmail(template, recipients) {
// Retrieving the email's subject and content in the requested sheet.
values = getRange(template).getValues();
var subject = values[0][1];
var message = values[2][1];
// Sending the email
MailApp.sendEmail(recipients, subject, message);
}

// Sends a reminder email to everybody who registered his email
// address the 3rd (reminder) sheet.
function sendReminder() {
// Retrieving the recipients list in the 3rd (reminder) sheet.
var values = getRange("Rappel").getValues();
var recipients = "";
for (var i = REMINDER_ROWS_OFFSET ; i < values.length ; i++)
{
if (i > REMINDER_ROWS_OFFSET) {
recipients += ", ";
}
var row = values[i];
recipients += row[0];
}
// Sending the email
sendEmail("Mail rappel", recipients);
}

The “sendEmail” function loads the email template in the sheet named in the arguments and sends it by simply calling the “MailApp.sendEmail” function. You can send 500 emails a day with this method. To check how much remaining emails you have, use the “MailApp.getRemainingDailyQuota”. The limit of recipients per email is 99.

The “sendReminder” function retrieves the email addresses list in the “reminder” sheet and formats it as a string that contains the addresses separated by a comma : it’s the email’s recipient. Then it calls the “sendEmail” function.

Step 4 : the order script

This script is like the previous one but the recipient and the email template are located elsewhere.

// Sends an email that contains a link to the order
function sendOrder() {
// Retrieving the recipient in the main sheet
var values = getRange("Commande").getValues();
var recipient = values[2][1];
// Sending the email
sendEmail("Mail commande", recipient);
}

Step 5 : triggering the scripts

If you add an “onOpen” function in your script, it will be executed each time the document is opened. You can use it to add a new menu in the spreadsheet’s UI that will allow you to manually trigger the scripts. The menu will be populated with the items of a 2-dimensions array. Each entry is composed of 2 items : the action’s name and the callback which is the name of the function that will be called when the user clicks it. You may have to wait a few seconds after opening the document before the new menu appears.

// Adds a menu in the user interface when the spreadsheet
// is opened.
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Réinitialiser commande",
functionName : "resetSheet"
},
{
name: "Envoyer commande",
functionName : "sendOrder"
},
{
name: "Envoyer rappel",
functionName : "sendReminder"
}];
sheet.addMenu("Sandwichs", entries);
};

Sept 6 : automation of the process.

For the last step, you will set up the cron so the reset and the reminder scripts will be automatically executed at a given time. The order script could be automated too but I don’t recommend it since it will send a email to people you don’t know or work with. It’s always better to keep an human to do this. Anyway, it’s a one-click action and it shouldn’t take too much time.

Click on the “Resources” menu > Current project’s triggers. You should see a list of the current automated triggers, add a new one.

spreadsheet_triggers

The “run” option allows you to choose the function that will be ran at a given event and the “events” options allows you to set up that event. There are 2 kinds of events : from the spreadsheet and time-driven. The first category of events are triggered by the spreadsheet. A good example is the “onOpen” function that is called when an user opens the document. An event can also be triggered when the document is edited or submitted. The second category of events happens at a given time or at a regular time interval. I selected “day timer” and “midnight to 1 am” for the reset script so it will happen by night when nobody uses it. Note that you cannot choose a specific time but a one-hour interval. I selected “7am to 8am” for the reminder script so it will happen just before my colleagues arrive at work. Don’t forget to check the time zone.

You can choose to be notified by email immediately or with a daily report if the script execution fails.

Conclusion

The Google API has very much more to offer than this but you learnt the basis. Now you can take a spreadsheet with any kind of content and automate some maintenance operations. I hope you found this little guide useful.

Revision

  • 07/03/2013 : If you share the spreadsheet with the link or set it public, the people who aren’t connected with a Google account will not see the new menu.
Advertisements