Month: May 2020

Categories
Cloud Apps

How To Create A Secure S3 Website Using Cloudfront & AWS Certificate Manager

If you have made the migration to host your website using Amazon’s S3 service and encounter issues with serving your content through a secure certificate or even trying to get Cloudflare to connect then this article will hopefully help set your connection right.

To help illustrate the concepts behind the connection, I’ll use a site I once owned to show the entire process freeworksheetsmath.com.

Step 0 – Log in AWS & Cloudflare Accounts

This is perhaps the easiest out of all the steps, although the rest aren’t too bad, but to get started you will need to log in to your AWS and your Cloudflare accounts.

Our first X steps will be in the AWS area, then towards the end we finish up with Cloudflare.

So, starting in your AWS account let’s get the following configurations set up.

Step A – Create an S3 Bucket (or two)

If you haven’t done so already you will need to create an S3 bucket to be able to host your static files.

Click on the orange Create bucket button and enter the web address of your website. For example, if your web address is www.some-site.com then you would enter this into the Bucket Name field. If you want to host your website from a specific region then select this region in the next field. Lastly, as our static site is to be viewable by the public we need to untick the Block all public access checkbox and confirm this selection by acknowledging the warning that pops up underneath.

For my old website this creation of the S3 bucket stage would look something like this:

Create S3 Static Website
Create bucket for static hosting in Amazon S3

You’ll know everything has worked when you see a green confirmation pane, and a listing of all your S3 buckets.

Configure Your S3 Bucket for Static Hosting

There are a couple of further configurations still needed on our bucket to enable it for static site hosting. Therefore, click on your S3 bucket in the table before you and from this new page click on the Properties tab.

On the properties screen we have a couple of options to further enable static site hosting, the most obvious being the tile labelled Static website hosting – click anywhere on the white space in this tile (i.e. everything but the Learn More link):

Set Static Hosting Properties S3
Set static website hosting details for S3 bucket

The available options on this tile should be fairly obvious, we want to select the option to Use this bucket to host a website and upon selecting this option we need to set what the index document is when people enter our web address in their web browser, and what the error document is when people type an incorrect web page for our website.

As I use Hugo to generate my static sites, it automatically creates a 404.html page as the error page, therefore my settings for my S3 bucket would be as follows:

Set S3 Static
Set S3 static website index & error pages

Before we leave this window you’ll need to grab the URL. The URL generated by AWS for my S3 bucket is http://freeworksheetsmath.com.s3-website-ap-southeast-2.amazonaws.com. Keep a copy of this URL as it will be needed later.

When you’ve copied the URL click the Save button.

Create Bucket Policy

Once you’ve set the properties of the bucket, you need to set the Bucket’s policies.

Click on the Permissions tab, then click on the Bucket Policy button.

Below the main text box is a link labelled Policy Generator, click this, and you will be navigated to a visual editor where you can set your permissions.

On this screen set the following values:

  • Step 1: Select Policy Type = S3 Bucket Policy
  • Step 2: Effect = Allow
  • Step 2: Principal = *
  • Step 2: AWS Service = Scroll down and tick GetObject
  • Step 2: Amazon Resource Name = Type in your bucket according to the format arn:aws:s3:::/ (mine would be aws:aws:s3:::freeworksheetsmath.com/)
  • Step 2: Add any conditions if needed (I didn’t set any).

Here’s how my options appeared:

S3 Bucket Statement
Add S3 bucket statement to bucket policy

Click the Add Statement button, and your settings would be added to the policy:

S3 Statement
S3 statement added to bucket policy

To generate the JSON code to use in our Bucket Policy you need to click the Generate Policy button found in Step 3.

Doing so pops up a modal window where you need to copy the contents of that window and then paste it over to the text box area above the Policy Generator link.

The code generated for my S3 bucket was as follows:

{
  "Id": "Policy1590953744901",
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1590953064638",
      "Action": [
        "s3:GetObject"
      ],
      "Effect": "Allow",
      "Resource": "arn:aws:s3:::freeworksheetsmath.com/*",
      "Principal": "*"
    }
  ]
}

When I copied this back into the bucket’s Bucket Policy text area and then clicked Save you should see confirmation of the nature of your bucket’s policy being public.

S3 bucket Policy JSON
Set S3 bucket policy JSON

Your bucket has now been set up to correctly display web pages. You can upload a file to your bucket and test this works by navigating to the URL you copied earlier.

Test S3 Bucket

To test your S3 bucket will correctly display web pages, create a simple index.html (or whatever identifier you used when setting the website’s index page in the Properties window).

I created a simple web page as follows:

<!DOCTYPE html>
<html><body><p>Hello world!</p></body></html>

Then upon clicking on the Overview tab in the bucket, click on the Upload button.

  1. Add your files from your computer. Click Next.
  2. Set permissions (I left everything as is). Click Next.
  3. Set the properties of the file (I left it set to Standard). Click Next.
  4. On the Review screen click Upload.

To test your page works go to the URL you copied in your web browser.

Test S3 Bucket
Test S3 bucket works by navigating to your bucket’s URL

If you can see the text of the HTML document you uploaded into your bucket then well done! If not, click on the Properties tab in your bucket, then click on the Static Website Hosting tile and click on the link to your bucket.

If you still cannot see anything check the settings on this tile for the Index document, and the name of the file you uploaded – they need to be exactly the same.

Create Another Second S3 Bucket

If you’d like to create another S3 bucket which automatically routes traffic through to your previous S3 bucket you’ve just created then do so now.

Go back through the previous steps, except this time name the bucket differently according to the web address you wish to redirect.

For example, I didn’t use the www sub-domain for my website address, but I wanted to redirect users who type the popular sub-domain in their address bar so that it redirects them correctly to freeworksheetsmath.com. Therefore, I created a new bucket labelled www.freeworkeetsmath.com.

The only difference with this set up is we only need to set the Properties on this bucket.

Therefore, once the bucket has been created, click on the Properties tab, then click on the Static website hosting tile again.

You’ll need to set the properties here a little different from your main bucket. Click the Redirect radio button and enter the name of your main bucket and whether to redirect traffic to a secure website or standard.

Here’s how the properties of my redirected bucket looked:

Redirect S3 Bucket
Set S3 bucket redirect properties

Click Save, and your redirected website is good to go!

Step B – Create SSL Certificate

If you want your bucket to be served through a secure connection (highly recommended) then you’ll need to use another service in AWS called AWS Certificate Manager.

By clicking on the Services link in the upper-left, type Certificate Manager and select the option that appears.

To start this process click the Get Started button to Provision certificates.

IMPORTANT!
I found I had to set the zone for my certificates to N. Virginia to get them to work properly with Cloudflare. If you experience any problems with connecting your certificates to Cloudflare you might want to do the same.

To continue through you will want to select Request a public certificate, upon clicking the Request a certificate button you will be presented with a list of steps, let’s go through these one by one.

Step 1 – Add domain names

Add the domain names you want this certificate to cover.

I wanted to be specific, so in this area I entered each of the following domains:

freeworksheetsmath.com
www.freeworksheetsmath.com
Domain Names
Add your domain names one at a time

Click Next.

Step 2 – Select a validation method

With this step you need to prove you own the domain by performing one of two tasks: modifying your DNS records, or through email verification.

If you’re comfortable and have access to your domain’s DNS records then select the DNS Validation option.

Step 3 – Add Tags

If you’d like to manage your certificates by applying tags you can do so here, otherwise click Review button.

Step 4 – Review

The final step is to review everything you’ve done. If you’re happy with everything then click the Confirm and request button.

Step 5 – Validation

On this final step, if you’ve previously selected DNS validation you will be required to create a unique CNAME record for your domain.

AWS Certificate Manager DNS
Validate by DNS in AWS Certificate Manager

Here I need to set the CNAME record to verify both the root domain and the www sub-domain.

Once you’ve set these properties in your DNS provider you need to wait for AWS to validate and confirm your domain.

AWS Validated Certificate
AWS Validated Certificate for your domains

Step C – Create Cloudfront Distribution

Our final step is connecting both the S3 bucket with the secure certificates generated.

Click on the Services link again in the top-left hand corner and enter Cloudfront.

Click on the Create Distribution button to get started.

Step 1 – Select Delivery Method

Click on the Get Started button for Web.

Step 2 – Create distribution

On this screen you need to set a few properties, as follows:

  • Origin Domain Name = Paste the URL of your bucket’s website address without the leading http value. For example, the URL of my S3 bucket is http://freeworksheetsmath.com.s3-website-ap-southeast-amazonaws.com but I just pasted freeworksheetsmath.com.s3-website-ap-southeast-amazonaws.com into this input field.
  • Viewer Protocol Policy = Set to Redirect HTTP to HTTPS
  • Compress Objects Automatically = Yes
  • Alternate Domain Names (CNAMEs) = I entered per line each of the domains I had registered with the Certificate Manager, namely:
freeworksheetsmath.com
www.freeworksheetsmath.com
  • SSL Certificate = Set Custom SSL Certificate (example.com) and then select the domain of the Certificate you created previously.
  • Default Root Object = Set index.html or whatever you set in your websites properties when setting the Static website Hosting.

Additionally, change any of the other settings as needed. Once you are done, click on the Create Distribution button.

You’ll need to wait a few minutes while the distribution creates the necessary configurations, and you’ll know everything is ready when you see the state of the distribution as Enabled

Cloudfront Distribution
Cloudfront distribution has been enabled

Step D – Set Domain CNAME Record

Finally, to finish this whole process off all you need do is set the unique Cloudfront URL to the value of your domains in your DNS provider.

My Cloudfront URL is d2i7zzjw0icbrm.cloudfront.net, therefore, the property value for the CNAME records of my root domain and www sub-domain would be this Cloudfront URL.

To check everything has connected properly, and the domains have propagated through you can test this by running a dig command in your terminal:

$ dig freeworksheetmath.com

When you see some A records with IP address numbers then you’ve likely got things connected.

The ultimate test is to type your domain web address into your web browser and see the page you uploaded to your S3 bucket back in [Step A]({{< relref “#test-s3-bucket” >}}).

Conclusion

In this article we have learned how to create a secure website using Amazon S3, AWS Certificate Manager and Cloudfront.

You should be able to click on the lock icon in your web browser to inspect the secure certificate served.

Here was the final result of my website:

Secure Connection
Testing your connection is secure, click on the lock icon in your web browser
Certificate Valid
Checking the certificate’s validity

Categories
Coding

3 Easy Steps To Create A Redirect Layout In Hugo

If you’ve created a static site using the wonderful Hugo package and need to create some simple redirect HTML web pages then you’ll need to do the following:

  1. Create your HTML template and place it in your main layout folder.
  2. Create an archetype template file.
  3. Create a file using the archetype and fill in all the meta header information.
  4. Publish your page.

Creating a simple redirect layout in Hugo is relatively easy and can help if you are transitioning from other content providers such as WordPress where your Hugo website structure may slightly change for some posts and pages.

Let’s explore these steps in a little more detail:

1. Create HTML Template

To create a redirecting HTML page we need to create a new HTML file and folder in Hugo.

As we are creating one page to represent one redirect, we will name the file single.html and place it in a new folder which we will label redirect.

When you have done this your folder structure should look something like this:

hugo_root/
  ├── archetypes/
  │   └── default.md
  ├── content/
  │   └── posts/
  │       └── my-blog-posts.md
  ├── layouts/
  │   └── redirect/         <--- CREATE THIS FOLDER
  │       └── single.html    <--- AND CREATE THIS FILE
  ...

Barebones HTML Redirect File

The contents of the newly created single.html file should be as follows:

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<head>    
    <meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
</head>
<body></body>
</html>

Barebones with Google Analytics

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<head>
    {{ template "_internal/google_analytics_async.html" . }}
    <meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
</head>
<body></body>
</html>

Barebones with Link in Body

<!DOCTYPE html>
<html lang="{{ .Site.Language.Lang }}">
<head>
    {{ template "_internal/google_analytics_async.html" . }}
    <meta http-equiv="Refresh" content="0; url='{{ .Params.redirect }}'" />
</head>
<body>
<!-- uncomment if you want to insert your template's header {{ partial "header.html" }} -->
<p>Please click this [link]({{ .Params.redirect }}) if you are not redirected.</p>
<!-- uncomment if you want to insert your template's footer any {{ partial "footer.html" }} --> 
</body>
</html>

2. Create Archetype Template File

To make it easier for us to remember the content needed in a redirect post we will create an archetype template file with all the needed data.

Therefore, in your archetype folder create a file labelled redirect.md – this must be the same name as the folder created in layouts in the previous step.

Your folder structure for your site should look something like this:

hugo_root/
  ├── archetypes/
  │   ├── redirect.md      <--- CREATE THIS FILE
  │   └── default.md
  ├── content/
  │   └── posts/
  │       └── my-blog-posts.md
  ├── layouts/
  │   └── redirect/         
  │       └── single.html    
  ...

Archetype Template

Then within this file populate the contents like so:

+++
draft = true
date = {{ .Date }}
type = "redirect"
slug = "{{ .Name }}"
redirect = "https://..."
+++
CONTENT NOT RENDERED

Only insert details here if you want to enter any additional detail about the purpose of the redirect, otherwise you can just leave this portion of your file blank.

There are several important things here to note:

  1. There needs to be a type meta tag which matches the name of the layout folder we just created.
  2. The content are of the markdown post isn’t rendered, therefore you can type whatever you want in here. Maybe even an explanation on what to do in case you forget!
  3. Remember when you’re ready to flick this redirect live change the draft from true to false or remove the draft line completely.

3. Create Redirect Page

To create a redirect page we open up our terminal, in our current Hugo website working directory and enter the command:

$ hugo new redirect/test-my-redirect.md

This should create a new file in your contents folder, such that your directory now looks like this:

hugo_root/
  ├── archetypes/
  │   ├── redirect.md      
  │   └── default.md
  ├── content/
  │   ├── redirect/
  │   │   └── test-my-redirect.md   <--- HELLO WORLD!! =)
  │   └── posts/
  │       └── my-blog-posts.md
  ├── layouts/
  │   └── redirect/         
  │       └── single.html    
  ...

Upon opening this new file you should see something like the following:

+++
draft = true
date = 2020-05-27T16:26:14+10:00
type = "redirects"
slug = "test-my-redirect"
redirect = "https://..."
+++
CONTENT NOT RENDERED

Only insert details here if you want to enter any additional detail about the purpose of the redirect, otherwise you can just leave it blank.

From here you can edit this file where needed, especially the redirect tag and then remove the draft tag when finished.

4. Publish

Check your redirect file is working first by loading up the hugo local server:

$ hugo server

Test the link by visiting the url: localhost/test-my-redirect

If all works well you should hopefully experience the redirect!

Conclusion

In this article we were able to learn in Hugo:

  1. How to create a custom template layout.
  2. How to create an archetype to help use the layout needed for this type of layout template.
  3. How to create a new page using the archetype and its corresponding layout.
  4. How to publish our new layout and test.

Hopefully your redirect issues in Hugo will be solved with this helpful post.

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 Flip ARRAY_CONSTRAIN Formula in Google Sheets

Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function.

Then I came across another handy Google Sheet function ARRAY_CONSTRAIN().

What ARRAY_CONSTRAIN Formula Does

There are three parameters with this formula:

  1. range – insert the range for the formula to operate on.
  2. num_rows – set the number of rows to compress.
  3. num_cols – set the number of columns to compress.

All are required fields.

Example

Let’s look at a current depreciation problem I’m trying to solve.

I’m trying to compress the different types of depreciation on an array of different purchases projected over the next few years and to calculate what the new depreciation would be once these items have been purchased.

The data looks like this:

{{< figure src=”/images/purchase-schedule-with-depreciation-life.png” alt=”depreciation schedule” caption=”Simple depreciation schedule” >}}

What we’re trying to achieve is to determine what the depreciation would be each year according to the year when items have been purchased, and their corresponding depreciation life in years.

Limitations of ARRAY_CONSTRAIN

Unfortunately the big limitation of ARRAY_CONSTRAIN is it works from the top-leftmost corner to cut the excess cells.

For example, if we apply the following formula on our data set:

=ARRAY_CONSTRAIN(C1:G6, 3, 2)

It will return for us the following highlighted cells:

Array_Constrain Top Left Cells
ARRAY_CONSTRAIN() function only returns data from top-left cell

This causes a problem for our depreciation schedule as we’d like to fetch the last two years when we’re in the 2024 column for row 3, so using the following formula:

=ARRAY_CONSTRAIN($C3:G$3, 1, B$3)

For the Network Upgrade row would only return [20000,<blank>], as these are the nearest to the top-left cells.

Workaround to ARRAY_CONSTRAIN

Is there a way to flip the input row?

There is!

Is there a REVERSE() function in Google Sheets we can apply to the range?

Unfortunately not yet.

So what we need to do as a workaround is to create our own REVERSE() function by sorting our data and reversing it backwards.

Thankfully my data already has a year row (Row 1), all I need to be able to do is sort the data set by that row, apply descending sort and then insert the new range back into the ARRAY_CONSTRAIN() formula.

Here’s how I flipped my data range:

=ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3),"SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0),$B3,1)

Let’s break apart this function and understand what’s happening, as usual we’ll start with the innermost functions and work out way out:

  • ROWS($C$1:G3) – I used this to be able to get the column number to return from the QUERY function. As the last Column in the data set is the one I wanted, I just obtained this from the height of the original input range.
  • TRANSPOSE($C$1:G3) – as my data set is in rows, I need to translate this into columns for it to be functional as a data set with the QUERY() function. The data set needs to include the row containing the years.
  • QUERY(..., "SELECT Col" & ... & " ORDER BY Col1 DESC", 0) – the second SELECT parameter to our QUERY() function fetches just the one column, but orders by the first column being the column containing all the year values. The third parameter 0 just makes sure we don’t return any header rows.
  • ARRAY_CONSTRAIN(..., $B3, 1) – we finally wrap everything back into our original ARRAY_CONSTRAIN function. This time we change the parameters a little as they are in a column format, but the purposes are still the same – we want the nearest 2 years (the value of cell $B3) – and as there’s only 1 column our third parameter is 1.

To calculate depreciation on this row our final touch is to sum everything returned from the range and divide by the number of years:

=SUM(ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3, "SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0), $B3, 1))/$B3

We further wrap the formula in a check to see if there are any depreciation values in the Dep’n (Years) column:

=IF(LEN($B3),SUM(...),"")

Result

Here’s the final result showing the function as well as our accumulated depreciation for the items purchased:

Calculate Depreciation Using Query & Array_Constrain
Calculating different depreciation schedules using QUERY and ARRAY_CONSTRAINT in Google Sheets

Conclusion

In this post you’ve been able to discover and learn about the ARRAY_CONSTRAINT. You’ve seen its limitations in fetching from the upper-leftmost corner, and perhaps in time Google Sheets will append an additional parameter to set where the corner should be fetched from.

In the meantime we’ve discovered how we can reverse or flip a range to make use of the ARRAY_CONSTRAINT function by using the TRANSPOSE and QUERY functions together.

Categories
Cloud Apps

Ignore Blank Cells in QUERY Function Google Sheets

If you have a series of data where you’d like to apply the QUERY function over, but want to ignore certain rows if a particular cell is blank or empty then you would write your formula as follows:

=QUERY(DataRange, "SELECT * WHERE Col1 IS NOT NULL", 1)

The way you can have the Google Sheets QUERY function ignore blank or empty cells is by using the string IS NOT NULL in your formula.

Example

As an example, suppose we had the following data on assets we’d like to purchase that have differing depreciation rates, but we only want to return the items where there is a depreciation value (implying purchases with no depreciation will be written off in the year they are purchased):

QUERY Function Remove Empty Cells
Remove blank cells from QUERY

In the above diagram we have the following function:

=QUERY(A2:E8, "SELECT * WHERE B IS NOT NULL", 0)

Here’s what each of the parameters passed into the QUERY function mean:

First Parameter – Data Range

Set the data range for which the QUERY function will be performing its task.

In the example above the QUERY is performed on the data inputs for each of the large purchase rows.

Second Parameter – Query Filter

Applying a filter on the data range by using a form of SQL language. In our example, here’s what the statement translates into:

  • SELECT * – select everything from the range;
  • WHERE B IS NOT NULL – apply the filter on column B where the cell is not null (is not blank or empty).

Third Parameter – Header Rows

Although our data set does contain a header row (row A) we don’t want to return this in our result, therefore, we set this value to 0.

Solution

The result of our formula is values from the original data set which need to be depreciated over several years.

We can further apply more logic to our QUERY function to perhaps SUM the value of all assets purchased containing the same depreciation value.

Conclusion

In this post we’ve explored the powerful QUERY function and how we can filter our data by excluding rows where they contain cells which are blank or empty.

By applying the condition IS NOT NULL in our QUERY filter we can easily remove these from our results.

If you’d like to learn more about QUERY functions, especially how we can merge multiple rows into one you should check out our illustrated step-by-step article.

Categories
Cloud Apps

4 Simple Steps to Merge 2 Columns Into 1 With QUERY Function

If you have two or more columns of data and you want to merge these columns into one column then using the Google Sheets QUERY formula may be one way you can achieve this.

Here’s an in depth explanation of how to achieve this:

Step 1 – Prep your data

If you data doesn’t contain any spaces then you’re good to go, if though your data does contain spaces then you will need to define a character at the end of each cell to get this to work.

Step 2 – Understanding the QUERY Function

Let’s have two simple data sets, one which does not contain spaces in its data set, and another which does:

Simple data set with QUERY no header
Simple data set showing QUERY function with no header
Data set with spaces & QUERY with no header
Simple data set containing spaces showing QUERY function with no header

With both data sets we have highlighted in grey the returned values from the QUERY function.

QUERY parameters

The QUERY function is a powerful formula in Google Sheets helps to treat data contained in tabular format like a database table which we can perform basic SQL queries on.

This function contains three parameters:

  1. The data set.
  2. The SELECT command.
  3. The number of headers in your data set.

It’s the third parameter of the QUERY function that will help us in being able to concatenate the data within each column.

At the moment, from above, we have set the third parameter to 0 which means our data set does not return any headers.

But what if I want to define the first row as being the header?

Then we would simply change this third parameter in the QUERY function to 1 – representing the first row. When we perform this change to our formula we see no change to our data, however, the returned values have now defined that first row as a header area.

Define header row in QUERY function
Defining the first row as header

QUERY Header Parameter

What if we were to increase the number represented in our QUERY function? How is the result set returned if we define the header parameter as 2?

Here’s how it looks for our data sets:

Data set QUERY with 2 header rows
Query function with 2 header rows defined

Did you notice what the QUERY function does when we define more than one header row?

It concatenates the header rows and inserts a space between each appended element. Therefore, the first row containing A and the second row containing B produce the header A B.

(Can you foresee the issues we’re going to run into when we merge our columnar data which contains spaces already?)

Data set with spaces 2 header rows
Query function with 2 header rows defined

So if we wanted to get all the elements within our column wrapped into one row we would just need to change that third parameter to the maximum height of our columns. We could use the ROWS() function to capture this number:

Use QUERY Function to Merge Column Into 1 Row
Capture all rows into the header

Step 3 – JOIN columns

As the columns have been merged into one header row, we now want to stitch these column headers together.

This is achieved by wrapping the QUERY function in a JOIN function, like so:

JOIN QUERY data set header rows
JOIN query header rows together to combine into one cell

Notice here we used the first parameter of the JOIN function as a space to mimic the behaviour of what the QUERY data did to our data too.

Do be aware the JOIN function does have a limit of 50,000 characters. If you have a lot of data to concatenate you might want to break it up to smaller data sets.

What if our data contains spaces?

As you can see this strategy isn’t going to work too well as our result now contains too many spaces between data cells, and looks as follows:

JOIN data from QUERY for data containing spaces
The problem with this strategy is data containing spaces

Therefore, we’re going to need to improvise on the spaces in our current data set.

What we’re going to do is to modify our original data by substituting spaces for an oblique character that isn’t used in our data. For example, my current data set with names isn’t likely to have a tilde character ~.

Therefore, what we want to be able to do is to change our imported data set in the QUERY function so that we are operating on data that doesn’t contain any spaces – we want to be able to reserve these spaces for the explicit purpose of being able to split our data back into cells again.

One such way I achieved this was by changing our original formula:

QUERY(A1:B3,,ROWS(A:A))

To this:

QUERY(ARRAYFORMULA(SUBSTITUTE(A1:B3," ","~")),,ROWS(A:A))

What has happened with our first QUERY parameter?

Instead of referencing the original data set we modify the data set to substitute spaces for our oblique tilde ~ character.

The ARRAYFORMULA provides us the functionality of being able to operate on each cell within the range, and all we want to do is apply the SUBSTITUTE formula to each cell – if it has a space character replace it with the tilde character.

The result of our change should produce something like this:

Query ArrayFormula Substitute Spaces
Modify each cell in our data set by using ARRAYFORMULA and SUBSTITUTE

We’ve now achieved our purpose of reserving the space characters for another specific purpose which we’ll see next.

Step 4 – Transform back to one column

Our data is in one cell, split by the space character.

To stretch this back out into one single column all we need to do is SPLIT the data by the special space character and then TRANSPOSE it to leave the data as a column, otherwise it will remain in row fashion if we don’t.

Here’s what happens to our first data set when we SPLIT first:

=SPLIT(JOIN(" ", QUERY(A1:B4,,ROWS(A:A)))," ")
Split data after JOIN and QUERY
SPLIT data after JOIN & QUERY functions

Then our concluding formula for the first data set:

=transpose(split(join(" ", query(A1:B4,,rows(A:A)))," "))
Transpose data after split
TRANSPOSE data after SPLIT, JOIN & QUERY

What about our data set with spaces?

A little more work is going to be required with our original data set which contains spaces.

If we copy the above formulas into our working “space” formula here’s the result:

=TRANSPOSE(SPLIT(JOIN(" ",QUERY(ARRAYFORMULA(SUBSITUTE(A1:B3," ","~")),,ROWS(A:A)))," "))
Transpose data after split
How our ‘spaced’ data looks applying the same TRANSPOSE and SPLIT formulas

As you can see we need to remove at the end each of those tilde characters.

How do you think we could do this?

If you were thinking ARRAYFORMULA and SUBSTITUTE again – well done! You’re acquiring a new skill today.

Here’s our concluding formula:

=ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(ARRAYFORMULA(SUBSTITUTE(A1:B3," ","~")),,ROWS(A:A)))," ")),"~"," "))
ArrayFormula Substitute, Split, Join & Query Functions
Final formula for concatenating multiple columns into one using QUERY function.

Conclusion

So there you have it!

Within this article you’ve been able to learn the following function skills when using Google Sheets:

  • What the third parameter of the QUERY formula does.
  • How we can modify each cell within a range by using ARRAYFORMULA – specifically using the SUBSTITUTE formula.
  • How to handle different data sets with the QUERY function, especially if they contain space characters.
  • How to JOIN our QUERY data to form a single cell of data (careful of the 50,000 character limit).
  • How to SPLIT our data set back into cells.
  • How to TRANSPOSE our data set back into columns.

Well done if you’ve been able to follow along and understand these complex topics.

Categories
Cloud Apps

Easily Merge Multiple Columns Into 1 with Query & ArrayFormula

In today’s post I’d like to explain how I used the following formula to help merge multiple columned data into a different columned arrangement as the purpose of the result was to export the original data set into a CSV file for import into NetSuite.

Here’s the formula we will dissect:

=ARRAYFORMULA(SPLIT(ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A)))," ")),"~"," ")),";",TRUE,FALSE))

The problem

I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year.

An example of the raw data looked something like this:

EmployeeJan-2020Feb-2020
123 Smith, Bob2,0002,200
456 Doe, Jane3,0003,300
789 Doe, John 200

The original spreadsheet had all months of the year and is labelled as Data:

Original Data set Employee Salary per Month
Employee salaries per month

I had to import this data into NetSuite using the CSV Import process, which required changing the structure a little bit, so that the resulting CSV looked like so:

Employee IDEmployee NameFrom DateTo DateAmount
123Smith, Bob1/01/202031/01/20202,000
123Smith, Bob1/02/202029/02/20202,200
456Doe, Jane1/01/202031/01/20203,000
456Doe, Jane1/02/202029/02/20203,300
789Doe, John1/02/202029/02/2020200

As you can see with the requirements of the task, we need to be able to expand the data set to rows (as it’s currently laid out in columns), then merge the data back into one columnar set. Once we have achieved this we then use the handy File > Download > Comma-separated values (current sheet) option in Google Sheets.

Solution: Step 1 – Expansion

The first rule when manipulating data is to leave the original data set intact.

Therefore, we create a fresh new sheet and label this whatever name is appropriate for your needs. In this example we will name it appropriate to what we are doing: DataExpand.

In our expansion we want to achieve all necessary CSV fields required. This means we need to fetch the ID number from the original Employee field, and to copy the remaining data into another field for the Employee Name. Then we split the header column in our original sheet to a data range. Followed lastly with the amount in each date range.

Our first sheet, should look a little something like this:

Expand Employee Salary data
Perform the CSV requirement per month

Here are the details as displayed in the photo above:

  • Cells $B$1 and $G$1 contained the =COLUMN() reference to each of the months on the original data set. This just helps with creating an easy index function that uses that reference which can make it easier copying the same columnar set 12 times.
  • Row 1 this contains the following formula to extract the ID number from the Employee field. Here we check to make sure we have data in the original Data sheet, if we do we want to perform a regular expression where we just want all the digits, therefore, we use the regular expression of d+:
=if(len(Data!A2),REGEXEXTRACT(Data!A2,"d+"),"")
  • From $B$2 down we then use another regex formula to extract those digits and replace them with nothing. We also want to check if the employee received any salaries or wages, if they didn’t we don’t want to add them into the data set.
=if(index(Data!$1:$1000,row(),B$1),trim(regexreplace(Data!$A2,"d+","")),"")
  • From $C$2 down we just insert the ID that is needed for each row if we have a confirmed value in the previous column.
=if(len(B2),$A2,"")
  • For $D$2 down if we have a value in $B$2 then we get the value of the salaries for that respective month. We use the handy index function here to get that value:
=if(len(B2),index(Data!$1:$1000,row(),B$1),"")
  • For $E$2 down if we have a value in $B$2 then we get the column heading of the respective month using, once again, the index function:
=if(len(B2),index(Data!$1:$1000,1,B$1),"")
  • Lastly, for $F$2 down we get the value in the previous cell, if something exists, and calculate what the last day of the month will be for that cell:
=if(len(E2),date(year(E2),month(E2)+1,0),"")

As we have been mindful of the formulas used for each budget month, we now just need to copy cells $B$2:$F$1 and then paste this 12 times across. We could make it even easier by, after our first paste in cells $G$2:$K$4 changing cell $G$1 to be a formula: =$B$1 + 1:

Employee Salaries change header to formula
Change header row on first paste to formula

Then we copy this newly pasted range with formula edits across 10 more times.

Solution: Step 2 – Concatenation

The purpose of the next sheet is to zip up our columnar collections. This is a relatively simple step, however, there a couple of things to be wary of:

  1. Use TEXTJOIN when zipping up the columnar data rather than the JOIN or CONCATENATE functions. The primary reason for this is where there is no data in a cell TEXTJOIN creates the blank join, whereas the other functions neglect it. As an example, if we had the following table:
 ABC
1MissingPuzzlePiece
2Missing Piece

When using =JOIN(",",A1:C1) it produces the result Missing,Puzzle,Piece for the first row and =JOIN(",",A2:C2) will produce Missing,Piece for the second row.

Whereas using =TEXTJOIN(",",FALSE,A1:C1) produces the result Missing,Puzzle,Piece for the first row and =TEXTJOIN(",", FALSE, A2:C2) will produce Missing,,Piece for the second row.

As the TEXTJOIN function allows for the flexibility of the concatenation of your columnar data it’s import the data remain as it is represented elsewhere if there happens to be blank cells in your data.

  1. The other important aspect with this formula is that we want to replace the space characters within our cells as the space character will be used to join and split data back into columns again later. Therefore, it’s important at this step (if your data also contains spaces) to replace the space character with an oblique character that is not used anywhere in your data set.

I am going to replace my space characters within my data to the tilde character ~.

Therefore, I have created a new sheet, which I’ve labelled DataConcat and cell $A$1 has the formula:

=if(len(DataExpand!B2),SUBSTITUTE(TEXTJOIN(";",FALSE,DataExpand!B2:F2)," ","~"),"")

Be mindful when transferring the data across to adjacent columns that you don’t copy paste or drag across the formula, as each column here needs to reference the collection in DataExpand. For example, in the next adjacent cell $B$1 the formula is:

=if(len(DataExpand!G2),substitute(textjoin(";",FALSE,DataExpand!G2:K2)," ","~"),"")

You will need to do this 12 times for each month.

Substitute TEXTJOIN on Columnar data
Combine your data ready for merge using TEXTJOIN and SUBSTITUTE (if needed)

Solution: Step 3 – Merge

So here’s where we merge all the columns from the DataConcat sheet in Step 2 above into one big column collection.

=ARRAYFORMULA(SPLIT(ARRAYFORMULA(SUBSTITUTE(TRANSPOSE(SPLIT(JOIN(" ",QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A)))," ")),"~"," ")),";",TRUE,FALSE))

Wow, what a massive formula!

As with mathematics, we have to start with the inner most functions and work our way out. So let’s break up each component within this formula and explain what’s happening:

QUERY function

QUERY(DataConcat!A:L,,ROWS(DataConcat!A:A))

Here we use the QUERY function to capture the data we want to operate on (the first parameter), being all the columns in the DataConcat sheet, and we then want to combine these cells into one row. We achieve this by setting the number of headers (the third parameter) to the maximum number of rows in the DataConcat sheet. This means every row in the DataConcat sheet will be zipped up into one row!

The way the QUERY function zips everything up is by applying the space character to each new cell. This was why it was important for us to remove the space characters from our own data set before we applied this function.

If you’d like to know more about the QUERY function I wrote up a more detailed post here explaining this type of use.

MERGE – Using JOIN, SPLIT & TRANSPOSE functions

TRANSPOSE(SPLIT(JOIN(" ", QUERY(...)), " "))

The combination of these three functions is to merge the single row returned by the QUERY function into one column.

First we JOIN the rows together using the special space character which helps to delimit each individual row. Then we SPLIT by the space character which produces the rows across individual cells horizontally. Finally, the TRANSPOSE function translates the horizontal cells into vertical formation – a single column.

Unfortunately the JOIN function does have a character length limitation of 50,000 characters. You may need to break up your requirements or look at other means if this ends up being an impediment.

EXPANSION – Using ARRAYFORMULA

The last step is expand our singular column so that it can be represented as needed for CSV export. If we’re expanding data sets we need to use the ARRAYFORMULA function, as demonstrated here:

ARRAYFORMULA(SPLIT(SUBSTITUTE(transpose(...), "~", " "), ";", TRUE, FALSE))

The ARRAYFORMULA provides us with the ability to loop through each of the cells in our data set and to apply individual functions to them. In our case we want to:

  1. SUBSTITUTE back the oblique tilde ~ character with its original space character.
  2. SPLIT back the data we concatenated in the DataConcat sheet. However, we need to be mindful there may be instances where we have blank cells, and we want the SPLIT function to return the blank cells, therefore, we need to change our fourth parameter in the SPLIT function to FALSE (default is TRUE). By switching this to FALSE it turns consecutive delimiters like ;; into three separate cells, rather than just one cell.

With these functions all combined they produce the wonderful result intended, like so:

ArrayFormula Split Substitute Expansion
Expansion of our singular column using ARRAYFORMULA, SUBSTITUTE and SPLIT

Conclusion

Within this post we were able to learn how we can extrapolate data from one form to another using several intermediary steps and complex Google Sheet functions.

Hopefully this post has been helpful with learning:

  • How to organise your steps by leaving the original data set (use multiple sheets if needed).
  • How to use the QUERY function and especially its third headers parameter.
  • Why you would use the TEXTJOIN function over JOIN.
  • The limitations of the JOIN function.
  • How to expand your data set using ARRAYFORMULA with any function when needing to loop through individual cells in the result.
  • How to set the SPLIT function’s fourth parameter so that consecutive empty cells aren’t treated as one.
Categories
Cloud Apps

How to Easily Sum Columns with Dynamic Range

I had a case in a spreadsheet where I needed to test the cash flow of payments made against future loans.

However, when it came to scenario testing the cash flow we had the ask and answer the question on the viability of the organisations cash flow depending on the loan repayment schedule.

Would we be able to repay our loans back quicker? What would our cash flow look like if repayments were 10 years, or 15 years, or 20 years’ terms?

To enable the spreadsheet to calculate this on the fly, I had to find a way whereby I could set a field value to represent the number of years the loan was to be repaid and for the single row in the report reflecting the repayment schedule to show those repayments.

For those familiar with a cash flow statement you will know there’s generally a section within the Financial Activities to detail any financial repayments made, ours was labelled Principal Repayments as shown in the basic table below of the first few columns (as we had to perform a 20-year projection there were more columns than this):

RowsABC
1Cash Flow ReportYear 1Year 2
2-8Assumptions area……
9Operating Activities  
10Adjustments to Income Statement$B$10$C$10
11-19etc ………
20Total Operating Activities=sum($B$10:$B$19)=sum($C$10:$C$19)
21   
22Investing Activities  
23CapEx Purchases$B$23$C$23
24-31etc ………
32Total Investing Activities=sum($B$23:$B$31)=sum($C$23:$C$31)
33   
34Financing Activities  
35New Loans$B$35$C$35
36Capital Grant Receipts$B$36$C$36
37Principal Repayments$B$37$C$37
38Total Financing Activities=sum($B$35:$B$37)=sum($C$35:$C$37)
39   
40Total Net Cash Flow=sum($B$20, $B$33, $B$38)=sum($C$20, $C$33, $C$38)

Now the issue at hand was that the New Loans row was an input row. Management wanted to insert how much they were willing to borrow for each project, but they wanted to see the impact on the Total Net Cash Flow which was the sum of the Total Operating Activities plus the Total Investing Activities plus the Total Financing Activities.

So with an input field on the New Loans row, and the output field on another row Principal Repayments all I needed to do was one more input cell representing the Loan Terms (in Years).

The cell I used as the input for the loan terms is defined in cell $B$50.

So here’s how the Principal Repayments row ended up:

$B$37 = -sum(index($B$35:B$35,0,MAX(COLUMN(B$35)-COLUMN($B$35)-$B$50+1,1)):B$35)/$B$50

So let’s break this formula down, starting in the heart:

  1. MAX(COLUMN(B$35)-COLUMN($B$35)-$B$50+1, 1) this formula helps to determine the column to start our range capture.

Here’s what we would get if the formula was copied across different cells:

$E$35 = MAX(COLUMN(E$35)-COLUMN($B$35)-$B$50+1, 1)

Which for each reference and function call results in:

  • COLUMN(E$35) = 5
  • COLUMN($B$35) = 2
  • $B$35 = 10
  • MAX(5 - 2 - 10 + 1, 1) = MAX(-6, 1) = 1

For the cell $Z$35 which would contain this portion of the formula as MAX(COLUMN(Z$35)-COLUMN($B$35)-$B$50+1, 1) has the following results:

  • COLUMN(Z$35) = 26
  • COLUMN($B$35) = 2
  • $B$50 = 10
  • MAX(26 - 2 - 10 + 1, 1) = MAX(15, 1) = 15
  1. index($B$35:B$35, 0, ...) the nifty thing about the index formula is that it returns a value or the reference to a value. The parameters of the index function are: INDEX(reference, [row_offset], [column_offset]) and as we don’t want to change the row_offset this is represented as 0, but we do want to change the column_offset and this is where the MAX formula helped us. From the value achieved from the MAX formula we then receive a reference.
  2. sum(index(...):B$35) as we’ve captured the cell reference from the index function we can add the range notation : and have the sum value add all values from that cell to the current cell we are in.
  3. =-sum(...)/$B$50 lastly as we have added up all the new loans in the sum formula we then divide the total New Loans amount by the loan term.

So if we had this cash flow sheet spread out of 25 time periods out, by the time we got to the formula in $Z$37 it would look like this:

$Z$37 = -sum(index($B$35:Z$35,0,MAX(COLUMN(Z$35)-COLUMN($B$35)-$B$50+1,1)):Z$35)/$B$50

Thereby only getting us the last $B$50 (loan term) periods and dividing that amount by the loan term.

INDEX formula is a nifty little formula to learn!

Categories
Coding

Best Way to Install NodeJS on Mac OS

Once every year I make it a priority to nuke my Macbook Pro computer.

There are two reasons why I do this is to keep the apps I actually use, naturally removing the ones I no longer use, but don’t know that I no longer use them!

As I like to try new things it does mean I install lots of scripts and apps on my computer and because I might not actively remove these things from my computer when I no longer need them my computer gets bloated.

By nuking your computer frequently it helps to completely remove unused and unnecessary scripts and apps from your machine.

Keep essential files backed up in the cloud

Knowing that I nuke my computer annually it helps to be more mindful of placing essential files on the cloud. There may come a day where my computer decides it doesn’t want a human to initiate the nuke, it wants to do it itself! When that happens all essential files still stored on my computer maybe lost forever.

This then has helped to structure my thinking into what to do with files as I am working on them. For example, if I’m writing a blog post I ensure I’ve already sent it to my private BitBucket repository, or if I’m working on a spreadsheet I store this in Google Drive or OneDrive.

It’s important then to make sure you are aware of the type of document you are handling and how this can be backed up if you’d like to keep it.

Moving on to our topic today, even though I nuked my computer 1 month ago, I’ve realised today I need to install NodeJS.

The problem I have with NodeJS is having it automatically update itself. I don’t want to have to re-install the direct downloadable file from NodeJS every time there’s a patch update.

Wouldn’t it be great to install it once, and when needed, run a command for it to automatically update?

Thankfully there is such an easy process: Homebrew for Mac

So here were the steps I undertook to be able to reinstall NodeJS back on to my computer:

Check if NodeJS is already installed:

Before you install node you may want to check if you already have it installed. To check if node is already on my machine run node -v in your terminal. This simple command checks the version number of the currently installed node module on your computer.

$ node -v
bash: node: command not found

If you have node installed, you should see a version number pop up underneath your command. Great, there’s nothing more for you to do here! If though, you see the above, move on to the next step.

Do some Homebrew housekeeping first

Before installing node, let’s do a little housekeeping to make sure our computer is updated and everything is in working order.

a) Check your system has the latest updates by running the command brew update:

$ brew update
Already up-to-date

If you see Already up-to-date then you’ve got the latest updates on your machine. If not, you would have noticed a bunch of modules updated on your system.

b) Check your system is humming along well with no conflicts by running brew doctor:

$ brew doctor
Your system is ready to brew.

If you see Your system is ready to brew then your computer is ready to proceed. If not, please read through any of the instructions provided on how you can correct your modules.

Install NodeJS using Homebrew

Our final step then is to simply run the command brew install node:

$ brew install node

You should see lots of action, and to check you’ve properly installed it, re-run our first command in terminal node -v:

$ node -v
v14.2.0

Now you’re ready to use NodeJS.

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” >}}).