Categories
Cloud Apps

How to Create a Google App Script Library

In our previous post on clasp and Google App Script code we discovered how we can pull code down locally, do our edits and then push it back to Google Apps.

This is great when we’re working on code on the back-end of Google Sheets, or Google Forms, or any of the other great Google Apps.

However, what do you do when you want to write code, but you don’t want someone ‘accidentally’ editing your code?

Is there a way where you can write your code and then link to it within a project and have it run without the worry someone will break it?

Yes, of course!

We will still be using the fantastic processes of clasp, but this time we’ll be creating a new project. So instead of cloning a project to our local computer we’ll start by creating a new directory and creating a project there.

Create project

Create a new project with WebStorm of your favourite code editor. Initialise npm in your new directory (if you haven’t already done so):

$ npm init
...

Install clasp globally if you haven’t already done so:

$ npm install @google/clasp -g

Or check you have the latest version:

$ clasp -v
2.3.0

To create a new project it’s just a matter of making sure you’re logged in and progressing through the prompts:

$ clasp login

Then simply creating a new project:

clasp create [scriptTitle]

Create Ignore File

You’ll want to exclude files from being a part of the project, so you may want to look at creating a .claspignore file. My file tends to have these added:

node_modules/**
node_modules/**/.*/**
node_modules/**/.*
.idea
.git
.DS_Store

Add gas-lib

Before jumping into the code you may also want to install a developer dependency to your project if you’re interfacing with Google Apps. I’d highly recommend adding a library to help with code completion:

$ npm install --save-dev gas-lib

Write Your Code

The next part of the journey is all over to you as you write your code.

If this takes time, and you want to keep your code safely stored on Google’s cloud then feel free to run the following command while you work away:

$ clasp push

Create version

Then when you’re ready to publish your code for publication and use you need to create a version:

$ clasp deploy

You should see a version number followed by a hash which represents the Deployment ID your script.

Do I need to re-deploy if I make changes?

Should you need to make any additional changes after having deployed your code you can still clasp push the code to Google Apps, but instead of using clasp deploy you will use the following command:

$ clasp version

The difference with this procedure is that you will need to provide a description for the new deployment version. As I find I’m only maintaining one source code most of my descriptions have been annotated with “fix X”, but feel free to use a better descriptor to suit your needs.

For example, if I’ve just made a fix to the code base on my computer, I could annotate the description like so:

{{< figure src=”/images/clasp-prompt-provide-description.png” caption=”Provide description to code changes” >}}

Great!

So now you’ve pushed your code up to Google Apps, how can you use it in the backend of other Google Apps?

Get Your Script ID

All you need is your Script ID for other Google App Scripts to use it.

Thankfully it’s just a simple command away. In your terminal enter the following command:

$ clasp open

When you do your script will open up in a browser window and on the terminal prompt you will see a URL which would have the following schematic:

Opening script: https://script.google.com/d/<SCRIPT_ID_HASH>/edit 

You can either try to capture the SCRIPT_ID_HASH from the URL in your terminal, or browser window, or you can fetch your Script ID from within the browser window clicking on File > Project Properties, and you should see on the pop-up modal a field labelled Script ID.

Import Your Code

Finally, to be able to import your code into another project you from the other projects’ code editor click on the Resources menu then click on Libraries....

You should see an area from this modal window where you can paste your Script ID:

Insert Script ID from Google App Script
Insert your Google App Script ID into the ‘Add a Library’ field

When you do successfully add your code you have the option of giving the library a callable name. I prefer to keep this as the default name of the library as it helps to keep things easy to maintain. If you do change the name, you may find having to update the name each time you update your version!

Here I’m adding Google App’s OAUTH2 library to my code, which is the same process for adding your own:

Add Library to Google App Script Project
Once you’ve inserted the correct Script ID you should see the library pop up above

Once you’ve then correctly added this Script to your project you must select a version to apply. Here I’m just selecting the latest version. Should the Script author update their code you will need to manually open this Libraries’ modal window and select the new version.

Select Latest Library in Google App Script Project
Best to select the latest version which will be at the top of the list

To reiterate again there shouldn’t be a need to change the Identifer field on this window, but if you must then do so here.

The purpose of the Identifier is that this is the referring Object name that is used in the projects code to use the functions of the inserted library.

Once you’re happy with everything just click the Save button.

Use It In Your Code

From here on out it’s now just a matter of referencing the new library in your code by using the Identifier.

So if we were using the OAuth2 library in our code it would look something like this:

var init = OAuth2.createService('QBO');

Conclusion

In this post you’ve been able to learn how to create a custom library in Google App Script.

You’ve seen how easy it is to deploy a new script, how to update it (if needed), how to import this library into your new projects, and how to reference it in your new project.

Creating your own libraries in Google Apps will help you to:

  • Reuse common libraries when interfacing with certain Google Apps
  • Prevent anyone ‘accidentally’ clicking around and removing your code
  • Easier access to code and to update, rather than trying to find the Document, clicking Script Editor (etc)

Have fun using Google clasp in your projects!

Categories
Cloud Apps

How to Clone Google App Script Code to Your Local Computer

I’m starting a new project, and the requirements for this project is to write some Google App Script, copy the source code over to BitBucket. Thankfully there’s an easy way to store Google App Script locally to your computer and then push those changes back to Google as well as git commit changes and have the code stored safely on a remote repository.

Here’s how you too can set up your computer and environment up.

Install google/clasp

To install google/clasp jump onto your terminal, and if you haven’t (as I haven’t yet), initialise npm in your folder:

$ npm init
This utility will walk you through creating a package.json file.
...

After progressing through all the prompts I then run the command to install google/clasp by npm install clasp:

$ npm install clasp
Installing @google/clasp...
Installed @google/clasp@2.3.0

Your terminal prompt should inform you of the success of your google/clasp module installation.

Login to clasp

To have google/clasp know where to pull and push scripts to it needs to connect to your Gmail account. Therefore, the first command you need to run once you’ve successfully installed it is clasp login:

$ clasp login

A link then pops up underneath the prompt and when you click on it you will be redirected to a Google login screen where you will need to enter your credentials and provide the necessary permissions for the script to function properly. You will know everything is successful when you see back in your terminal window:

Authorization successful.

Clone script

If you have already started a Google App Script project you may be able to clone it to your current project. There are two ways you can try to pull in a project’s script:

  1. Try clasp clone and then select from the options presented by using your up and down arrow keys to navigate through the list.
$ clasp clone
? Clone which script? (Use arrow keys)
> ABC ...

If, however, you have many scripts, and the script you’re looking for isn’t presented you will need to exit from the prompt window using Ctrl-C.

  1. Enter the specific Script ID as a parameter to your clone command, like so:
$ clasp clone ABC123...

Where ABC123... is the Script ID you can obtain by opening up your Google App Script console for the project you’re hoping to clone and clicking on File > Project Properties. From the modal window that pops up, copy the Script ID hash.

Get Script ID for Google Clasp
Get your Script ID to easily clone a project to your computer using clasp

Ignore Files

If needed you might want to exclude certain files from clasp pushing them into your script folder in Google. If so, create a .claspignore file and populate it with files you don’t want to push up.

My .claspignore looks as follows:

.DS_Store
node_modules/**
node_modules/**/.*/**
node_modules/**/.*
.idea
.git

Update code

To update your code on the Google Server you just need to push your code back up.

$ clasp push

You can check your code is working by opening your project’s Google App Script code.

Conclusion

In this short article you’ve learned the following:

  1. How to copy a project in Google Apps to your local computer using the clasp npm package.
  2. How to exclude certain files and folders from your project.
  3. How to make local changes and push your changes up to Google Apps.

If you have your code wrapped as a library which other code references you’ll need to create a version and then reference the new version in your code dependencies.

If you’d like to discover how you can create a library for your common Google Apps code, or to prevent someone from editing your code on your Google Sheets or other Google Apps, then check out my [next post on creating a Google Apps library]({{< relref “2020-05-25-create-library-dependency-google-app-script.md” >}}).

Categories
Cloud Apps Coding

How To Assign A Blank Cell To A Default Value

Perhaps the easiest way to assign a default value to a variable is to append the || conditional after referencing the variable.

For example, after looping through an array and mapping the values to properties within an object, I needed to test whether the property had been assigned.

As I use Google Spreadsheets to iterate through data imagine the following data set:

ABC
1NameAgeChoice
2Ryan50A
3Sara27
4Jude29C
Data set containing rows of data, except for one row with a blank “Choice” value.

Upon grabbing this data in Google App Script using a quick one liner:

var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); 
// => [[ "Name", "Age", "Choice" ], [ "Ryan", 50, "A" ], [ "Sara", 27,  ], [ "Jude", 29, "C" ]]

We now have our sheet data wrapped into a 2-dimensional array.

If we now reduce this array into an object such that we can easily reference each element within the data variable we should easily be able to do like:

var obj = data.reduce( function( prev, el, i ) {
    var result = {};
    if ( i > 0 ) {
        result.name = el[0];
        result.age = el[1];
        result.choice = el[2];
        prev.push( result );
    }
    return prev;
}, [] );

Hopefully the above code is somewhat self-explanatory, but here we only have one condition and that is to remove our header row in our data and then mapped all elements within our array to a property – in essence corresponding to the header titles.

However, upon doing our data manipulation we’d now like to return it back to the sheet and therefore need to map it back to a 2-dimensional array. Here’s how that code would look:

var arr2d = obj.map( function( el ) {
    return [ el.name, el.age, el.choice ];
});

This may appear to look great, but if we were to inspect the arr2d variable we would find it showing this:

// => [["Ryan", 50, "A"], ["Sara", 27, undefined], ["Jude", 29, "C"]]

Most of that is fine except for the ugly undefined. Ugh! If we were to return this back to the sheet we would get the undefined word populating our sheet.

So what can we do?

There are a couple of things, but one such simple way is to apply the || conditional on that final map method, making it look like this:

var arr2d = obj.map( function( el ) {
  return [ el.name || "", el.age || "", el.choice || "" ];
});

// => [["Ryan", 50, "A"], ["Sara", 27, ""], ["Jude", 29, "C"]]

Now the result is looking much neater and we can return the results as is back to the sheet.

Anyway, be mindful of undefined when working with arrays and objects when sucking data out from Google Spreadsheets playing with it and then returning it back to the sheet. It could make for an unsightly sheet!