Google Spreadsheets can be even smarter! Let the Google Apps Script save your time

In this article, I want to look closer at the Google Spreadsheets app and show you how to make it even smarter using the Google Apps Script

Imagine that your job is about supervising someone else's work, which usually leads to writing some reports. You want to keep things standardized and generic, so you created a spreadsheet with the list of things you need to control and some cells for a comment. Let's assume that you are responsible for the quality of a digital product. Then your Spreadsheet may look like this:

In the example above, you can see several things:

• feature name ("User login form") and number (e.g. from an ERP system),
• list of aspects you have to check and give feedback on ("Area” column),
• some empty fields for your conclusions ("Comment" column).

This sheet is called "Report generator", because I'm going to show you how this document can evolve into a really useful tool, allowing you to assess the quality of someone's work results.

If you want to quantify some results, probably the best option would be to use a scale, e.g. 1-10 points, or something along these lines.

Let's create a new sheet in the same document, call it "Config" and define it as protected (or at least append a keylock emoji to its name). This sheet is only for keeping some predefined options, like the ones below:

Now you can put these values into some dropdowns in the Report generator sheet by using the Data validation feature. Here you can find some instructions on how to achieve this:

Having done that, you can easily convert ratings to numeric values by using the IF formula. Your spreadsheet will stay human-friendly and your conclusions will be quantified (so measurable and comparable) at the same time.

This document is neat, however, imagine what happens when the scope of your audit grows and you have hundreds of things to check. You can save an empty form template and copy it every time you need to make a new report, but switching between document lists and the spreadsheet is very annoying. This is the point at which you start thinking about some automation.

It’s the perfect time to start your adventure with the Google Apps Script!

Before we start coding, let's prepare some buttons for bulk actions. According to the example above:

• mark all items as "Perfect,"
• mark all as "Improvements needed,"
• mark all as "Unacceptable,"
• reset all (set value to an empty string).

Also, we will need one extra action to save a new file when the audit is done.

First, let's expand our Report generator with the functional area containing buttons.

Then, by using Insert -> Drawing, let's add our buttons, one by one.

Our sample document should look like this:

We're ready to add some logic to this beautiful UI! It's very easy to start – just choose Tools -> Script editor in the top menu and the browser will jump to a new card.

The editor's interface is very intuitive. If you are familiar with any code editor, you should easily rename your file or create a new one (File -> New -> Script file).

Google Apps Script is nothing more than a JavaScript's cousin, so to say. If you know JS, you can achieve GS proficiency, too!

The very first step is to collect all possibly needed references in one place and give them some meaningful names. We're starting with global variables by removing the default myFunction declaration and typing something more useful in the Code.gs file.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()

// sheets references

var report = spreadsheet.getSheets()[0]
var config = spreadsheet.getSheets()[1]

The above code is quite self-explanatory, but to make it absolutely clear:

• SpreadsheetApp class is available in a global context,
• getActiveSpreadsheet method allows us to get the reference to the spreadsheet we're working on,
• getSheets method called on the Spreadsheet instance returns the array of sheets from this spreadsheet; in our example, it returns the [Report generator sheet, Config sheet] array.

Now we can go further and grab some useful data from our spreadsheet.

// editable area (cells with dropdowns)

var dropdowns = report.getRange('C4:C13')

// cell with subtitle (feature name)

var featureName = report.getRange('B2').getValue()

// options from 'Config' sheet
// notice that you can pass a single cell to getRange() method
// and changing options doesn't affect your script's functionality

var values = {
  unacceptable: config.getRange('A2').getValue(),
  improvements\_needed: config.getRange('A3').getValue(),
  perfect: config.getRange('A4').getValue(),
  empty: ''
};

The only thing left to code is the definition of functions we want to assign to our buttons.

// button functions

function allPerfect() {
  dropdowns.setValue(values.perfect)
}

function allNeedImprovements() {
  dropdowns.setValue(values.improvements\_needed)
}

function allUnacceptable() {
  dropdowns.setValue(values.unacceptable)
}

function clear() {
  dropdowns.setValue(values.empty)
}

function copy() {
  var copy = spreadsheet.copy(featureName + ' - report')

  // Now we have copied two sheets with same values
  // but changed names (with a "copy" suffix),
  // in this case, we have to give them original names

  // rename report sheet
  copy.getSheets()[0].setName(report.getName())

  // rename config sheet
  copy.getSheets()[1].setName(config.getName())
}

The copy() method will automatically create a new spreadsheet named (in our example) "User login form – report”, right next to the current one.

Assign our methods to the buttons created before. Choose Assign a script after a right click on the button...

...and type the method's name, here it's allPerfect

You don't need to close your generator. Use your bulk actions to prepare the form for a new report, change the feature name and then save another copy!

Possibilities of Google Apps Script are so wide that discussing them all would take a whole series of articles. If you are interested in optimizing your office work, feel free to check out the official docs and make your sheets even smarter!

There are some really useful hacks out there, like this one from xfanatical.com about exporting Google Spreadsheet to PDF.

The example described in this article is here. Hope you'll love it. Have fun!

Read more:

- How to improve Vue.js apps? Some practical tips

- Quality first! 5 easy steps to lint your code with GitHub workflows in JavaScript project

- How to start programming in Ruby in 7 easy steps