Google Sheets and Docs are very powerful, flexible tools for data collection and analysis. But do you know that there's a lot more you can do with both Sheets and Docs, using free tools or just a bit of extra coding? And even if you are not a programmer? Do you know you can:
- Enable users to edit responses they have made in Google forms?
- Automatically copy (part of) data from one Sheet into another one?
- Simultaneously collect various metrics for your Google Analytics, YouTube and Twitter accounts?
- Automatically track Twitter posts around a Twitter handle, hashtag or search term?
- Automatically count the number of Twitter followers of various accounts and add them dynamically into a Google Sheet?
In this post and the next ones, I'm presenting few different options I’ve used to 'extend' Google Sheets and how I used them in the development of a program M&E system and dashboard for IDRC.
Today I'll look specifically at two possible use of Google Apps Scripts for Google Sheets.
 |
| Google Apps Script editor |
About Google Apps Scripts
Google Apps Scripts "is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications."With Apps Script, there's quite a lot that you can do, such as write custom functions, create macros and menus for Google Sheets. Google itself provides quite some guidance on how to work with Apps Script, but sure this may not be easy for a total beginner.
Luckily, there's plenty of kind (and clever!) people out there that have developed Apps Scripts and make them available to others online. And those you can just use!
Use Apps Scripts to collect Forms "edit response links"
In M&E system and dashboard developed for IDRC program, as we saw part of data collection is manual, with users inputting data for research outputs and pilots through a series of Google Forms. So what if users want to update/modify an existing entry?If you are familiar with Google Forms, you probably know that responses can be collected into a Sheet. You may also know that you can set up your Form so that, after an entry is submitted, it sends an email to the person that has contributed that submission. The email contains a link that the person can click to in order to modify/edit the entry.
Well this is sure nice and useful!
But wouldn't it be better if the edit response links were also added to the Sheet where the responses are collected, nicely ordered in line with the relevant form entry?
You can do this, with this Google Apps Script I've found browsing online.
To use this Apps Script, what you have to do is the following:
- Click on Tools >> Script Editor in your destination Sheet (as in the image on the right);
- In the Script Editor, copy this piece of code here;
- Change the parameters as indicated in the code;
- Save the script and run it;
- Click on Resources >> Current projects trigger and set the script to trigger at every new Form entry;
- Check that the edit response links are added in the right column on the destination Sheet.
Importing data from a different spreadsheet using scripts
While this Apps Script is very specific for when you use Forms, there are few others that can come in handy in more occasion. For example, to automatically copy (part of) data from one Sheet into another one.While you can actually do also this using in-line cells functions, as nicely explained in this post, I've found this to be not very reliable and not to always update automatically. So I would recommend to take the slightly more technical route and use Apps Scripts. You can find the link to the code and the explanation on how to insert this script.
Give it a try and see for yourself how it works. And let me know in the comments here if you are using other useful Apps Scripts that’s worth sharing.