Google Scripts Spreadsheet Duplication

The Problem.

So when working with a client a need arose to have an online application require a unique set of fields to fill out around a proposed budget based off an Excel Spreadsheet. Without getting too much into the weeds with the details it would have been a nightmare to try to “re-create” the spreadsheet with HTML form fields and javascript, especially with the typical “can we get it next week” client requests every dev-head is subjected to.

I suggested we use Google spreadsheets as the delivery vehicle. It made sense. It’s a spreadsheet everyone is familiar with, the only requirement would be the browser the user is already in, and the formatting is similar to what the end goals of the client requested.

All we needed to do was to create 1 Google spreadsheet and associate the unique URL’s with the 400 some odd users. Simple right! And a great solution to a difficult problem.

However… The client loved it too much…

And they wanted to utilize it 3 times for 3 different sets of data. Ugh, so 3 sheets for ~400 users, hardly easy to create or manage.

In steps Google Scripts with the intention of auto-creating the sheets. The best part was the ease of use. Google houses the script on its own servers. All I need to do is write it, test, and deploy as a “web app” (Publish Menu > Deploy as Web App…). This eliminates any external library importing, and no oAuth, as the scripts are already manually authenticated in the set up on their servers. And the script chaining, oh I love it. Here is a link to Google Scripts documentation on the Spreadsheets SpreadSheetApp Method that is mostly used in my scripts.

The gist of how it works.

I create a new sheet manually with the intention of being a “template”. Google’s ease of use makes it easy to straight copy and paste from Excel the columns. This imports not only the stylings and content, but the equations on the cells as well so no mistakes are made.

Now that the template exists, I need to write a Google Script that I can “hit” in my loop locally of users from my DB’s to create a copy of the template, save it to my google drive, retrieve the newly created sheet’s URL, and pass it back to my local script to store and associate with each user.

The code.

Google Scripts Code.
function doGet(e) {
//grab the id passed in of the google ss that I want to duplicate
var id = e.parameter.id;
var name = e.parameter.name;

//get the spreadsheet we want to duplicate and create a new sheet
var duplicateSS = SpreadsheetApp.openById(id);

//copy in the template to the new sheet and name it
var newSheet = duplicateSS.copy(name).getId();

//push the changes we committed above.
SpreadsheetApp.flush();

//get the url of the ss
var returnURL = SpreadsheetApp.openById(newSheet).getUrl();

//if we want to limit people by email and goole account requirements
//SpreadsheetApp.openById(newSheet).addEditor("username@email.com");

//if we want anyone to view with the link
DriveApp.getFileById(newSheet).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

return ContentService.createTextOutput(returnURL);
}

Http Request Code.

To make it work, write an HTTP GET request to your Google Scripts “web app” URL and pass in any parameters you may want to pass. In my case I’m passing in “id” and “name”. Id is in reference to the Id of the Google Spreadsheet “template” file I manually created, and name is an arbitrary value for me to be able to later associate which sheet is which if I need to look and find a particular sheet.

The code above is pretty straight forward. Open a sheet based off the Id passed in, duplicate the sheet, change the permissions, in this case I want anyone with the link to be able to access the sheet, but you can lock permissions to particular users via the “addEditor(“email address”);” or “addEditors([array of email address])” method.

The return utilizes Goggles ContentService method, because I’m really only interested in returning the single value URL provided by getURL();

Now copy() is a little vague for a named method, but copy() does more than copy, it creates, so all the duplication and creation is handled in one single call, sweet!

I thought this was a clever method to solve a problem with an already standardized and trusted source. Thanks to Google for making this easy.

A few notes.

Google Scripts deployed as a web script require doget(); According to the docs.

To create a web app with the HTML service, your code must include adoGet() function that tells the script how to serve the page.

Also, to see a saved change to a web scripts code, you must publish as a “version” when “releasing” your web script. It’s really simple, just when you go to Publish > Deploy as web app. You will see a “Project Version” drop down. Just select “new” and you have deployed your new script to be accessible.

Update:

Since everything is automated, we needed a way to check and see if the user actually made any edits to the file we provided before the user was able to submit their full application. Google made this easy with a events listeners for each of our templates. The super cool part is since we are copying the sheet template as a new sheet, it carries over not only the content, formats, and formulas, but also any attached scripts on the sheet. So we only need to attach our events listener once.

To create an event listener, go to (Tools > Scripts Editor…) and use Google’s onEdit() method.

The script.

function onEdit(e) {
// set our last updated cell to reflect a change

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("H1");
cell.setValue("edited:" + new Date());
}

Here we are setting a targeted cell “H1” to be our holder. We could validate against each cell we want to see if there is data in but this way is more general as we don’t really care what the user put in, but that they did at least edit the file at least once.

onedit() is fired when the user makes a (or any additional) changes, so once the edit happens our H1 field is filled out with a timestamp that we can check against with another web app call using cell.getValue() to see if anything is in there.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s