🐟Curtis Chan Chun Tsong

Follow

Dec 22, 2019 · 5 min read

A) Introduction
B) Function QUERY() and SQL
C) Macros and Google Apps Script
 C1) Interface Setup
 C2) Button to Search
D) Closing

本文亦附有繁體中文（香港）版本。
The traditional Chinese version (zh-HK) of this article is also available.

A) Introduction

History of volunteering supply insights for my organisation. To make the volunteer record more manageable to staff, different worksheets keep records from distinct departments. The event organizer will append the attendance after each successful hosting. Renewed every year, volunteers also have a well-designed booklet to keep track of how many hours they devoted.

Each department log sheet follows the same structure as the simplified example below.

Date, StartTime, EndTime, Hours, Event, Staff, Volunteer

By the end of the year, the most enthusiastic volunteers will be rewarded and mark their names in the annual organization newsletter and a ceremony next year. So frequent would they double-check the entries with us. Although the staff from each department can enter volunteer service log efficiently in their own separated worksheet, the backward record retrieval of a specific volunteer is difficult.

Still, there is a way to make data validation works, without extensive modification to the existing system.

B) Function QUERY() and SQL

=QUERY(data, query)

Google is powerful. It provides a unique function to apply SQL (Wikipedia), an easy to use query language popular in the database management system. Like a normal spreadsheet function, the QUERY() function can return data from the predefined criteria starting with an equal sign.

Since we have separated worksheets (i.e. tab AAA and BBB), let’s use a semicolon ; to merge them vertically.

In the second part, we pick Tom here as an example. “SELECT * WHERE COL6=‘Tom’ ORDER BY Col1” sets instruction to get any row chronologically that comes with the name, Tom, in the sixth column, or Volunteer from each worksheet.

=QUERY({AAA!1:500;BBB!1:500},”SELECT * WHERE Col7 =’Tom’ ORDER BY Col1")

By the way, may I remind the difference of the delimiter here, a single quotation mark in SQL and the double one in the Sheets formula.

C) Macros and Google Apps Script

The formula works fine but such syntax can be a nightmare for some colleagues. Let’s make it more user-friendly. Macros, a built-in function in Google Sheets, is what we gonna hide all the programming behind the stage.

C1) Interface Setup

Here, we create a new worksheet holds only two horizontal stretched cells. The cell A2 holds optional labels while A1 is the main search field for our automation setup. By the search field, we place a magnifying glass image that I’ll explain later.

Then, we head to Tools > Script Editor and save the following code.

function CheckTargetRecords() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.getRange(‘A1’).activate();
 var nameToSearch = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
 var formulaToUse = ‘=QUERY({\’AAA\’!1:500;\’BBB\’!1:500},”select * where Col7 =\’’+nameToSearch+ ‘\’order by Col1")’
 spreadsheet.insertSheet(nameToSearch);
 spreadsheet.getActiveRange().setFormula(formulaToUse);
}

In English, the purpose of the code above is to compile every row that related to the name we entered in the cell A1. The variable nameToSearch quotes the value from the cell A1 and passes it into one of the criteria in the function QUERY(). The script creates a new worksheet and inserts the integrated formula. In the end, it downloads all the matched entries there.

C2) Button to Search

My workmate can enter the volunteer name in the search field and run the script by going Tools > Macros > checkTargetRecords. Or, we can right-click the magnifying glass image to assign the macro to it as a button.

Now, we try “Mary” in the cell A1 and hit the “search” button.

Da! Da! Every volunteering records of Mary are listed in the new worksheet, even though the entries are scattered in different department logbooks.

Every contribution by our volunteer is so important and we can now get it right.

D) Closing

The current system is based on Google Spreadsheet. It can be a big change to move all the data to a database information system. Extra training can be costly and time-consuming for staff in an NGO.

See also

For more Google Sheets tips, check out my publication.

References

• QUERY function — Docs Editors Help
• Use macros and add-ons — Docs Editors Help
• Social Welfare Department — Central Office for Volunteer Service