Go to content
The Codest
  • About Us
  • Services
  • Our Team
  • Case studies
    • Blog
    • Meetups
    • Webinars
    • Resources
Careers Get in touch
  • About Us
  • Services
  • Our Team
  • Case studies
    • Blog
    • Meetups
    • Webinars
    • Resources
Careers Get in touch
2020-03-23
Software Development

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

Wojciech Bak

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

Nowadays, cloud solutions are pushing out desktop apps dedicated to office work. One of the biggest office suites is provided by Google and includes Documents, Spreadsheets and Presentations, so the majority of what an average office needs.

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:

software development

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:

Codest software development

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:

software house

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.

Fronented Report for 2020

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.

software house Poland

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

Codest software house

Our sample document should look like this:

software house Poland

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.

software development agency

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...

software development agency Poland

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

JavaScript software development

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

Related articles

Software Development

3 Useful HTML Tags You Might Not Know Even Existed

Nowadays, accessibility (A11y) is crucial on all stages of building custom software products. Starting from the UX/UI design part, it trespasses into advanced levels of building features in code. It provides tons of benefits for...

Jacek Ludzik
Software Development

5 examples of Ruby’s best usage

Have you ever wondered what we can do with Ruby? Well, the sky is probably the limit, but we are happy to talk about some more or less known cases where we can use this powerful language. Let me give you some examples.

Pawel Muszynski
Software Development

Maintaining a Project in PHP: 5 Mistakes to Avoid

More than one article has been written about the mistakes made during the process of running a project, but rarely does one look at the project requirements and manage the risks given the technology chosen.

Sebastian Luczak
Software Development

5 reasons why you will find qualified Ruby developers in Poland

Real Ruby professionals are rare birds on the market. Ruby is not the most popular technology, so companies often struggle with the problem of finding developers who have both high-level skills and deep experience; oh, and by the...

Jakub
Software Development

9 Mistakes to Avoid While Programming in Java

What mistakes should be avoided while programming in Java? In the following piece we answers this question.

Rafal Sawicki
Software Development

A quick dive into Ruby 2.6. What is new?

Released quite recently, Ruby 2.6 brings a bunch of conveniences that may be worth taking a glimpse of.  What is new? Let’s give it a shot!

Patrycja Slabosz

Subscribe to our knowledge base and stay up to date on the expertise from industry.

About us

We are an agile software development company dedicated to empowering our clients' digital transformation projects and ensuring successful IT project delivery.

    United Kingdom - Headquarters

  • Office 303B, 182-184 High Street North E6 2JA London, England

    Poland - Local Tech Hubs

  • Business Link High5ive, Pawia 9, 31-154 Kraków, Poland
  • Brain Embassy, Konstruktorska 11, 02-673 Warsaw, Poland
  • Aleja Grunwaldzka 472B, 80-309 Gdańsk, Poland

    The Codest

  • Home
  • About us
  • Services
  • Case studies
  • Know how
  • Careers

    Services

  • PHP development
  • Java development
  • Python development
  • Ruby on Rails development
  • React Developers
  • Vue Developers
  • TypeScript Developers
  • DevOps
  • QA Engineers

    Resources

  • What are top CTOs and CIOs Challenges? [2022 updated]
  • Facts and Myths about Cooperating with External Software Development Partner
  • From the USA to Europe: Why do American startups decide to relocate to Europe
  • Privacy policy
  • Website terms of use

Copyright © 2022 by The Codest. All rights reserved.

We use cookies on the site for marketing, analytical and statistical purposes. By continuing to use, without changing your privacy settings, our site, you consent to the storage of cookies in your browser. You can always change the cookie settings in your browser. You can find more information in our Privacy Policy.