Set the Paper Size and Orientation in a Doc Using Google Apps Script

Google Apps Script: DocumentApp setPageWidth, setPageHeight

Sometimes you need to prepare a Google Doc’s paper size and orientation programmatically using Google Apps Script.

Unfortunately, you can’t just call for say, A4 in Landscape. Okay, not until now (see my code below).

Google Apps Script does provide a way to set the dimensions of your page  in the body class by using:

  • setPageWidth(pageWidth)
  • setPageHeight(pageHeight)

The page widths and heights are measured in PostScripts Points which is a bit of a pain too.

Here is an example of setting and A3 paper size in Landscape.

Ugh. What a chore. You need to find the dimensions of the paper in points.

Enter this little nifty function and your life will be so much easier:

Continue reading “Set the Paper Size and Orientation in a Doc Using Google Apps Script”

How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script

Google Apps Script: SpreadsheetApp, getUI, HTMLservice, 

What if you want to get a value or a range from Google Sheets and show it in your sidebar using Google Apps Script?

First, you will need to get the value or range by using Googles server-side script. Then you will have to display it client-side in your HTML document.

Documentation on getting the server-side and client-side talking nicely to each other is a little vague. Hopefully, this very basic tutorial will help clear things up.

In this tutorial, I will also be using Jquery.

Let’s get started.

Continue reading “How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script”

Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way

Google Apps Script: SpreadsheetApp, getRange, getLastRow, getDataRange

I was trying to rush out some Google Apps Script code to deal with a task on Google Sheets recently. Basically, I had to search through a heap of data and find certain values and do something too them.

My column was reaching across the page to something like Column BK and my rows were over 1000 deep. Running this code was taking forever!!!

My immediate instinct was:

What have I done wrong?

…and my instinct was right.

The Good and Bad Way to Search Through Code

So after looking at my code again, I discovered that for some reason I go it into my head that I should be searching each cell for the value I needed and then doing something with it.

Sounds logical right? It’s sorta what you are meant to do.

The problem is that I was calling the sever and asking for the range in each cell as I was looping through the entire document. This is super costly and inefficient in terms of time.

Google talks about this in their Google Apps Script Best Practice page under Batch Operations.

Also, if you do run a costly code like this, then you will get a little red light in your Script tool bar that represents your Execution Hints:

Google Apps Script Execution Hints

Clicking on Execution Hints and expanding the side bar with provide you with a far-too-deserving-polite dressing down about your slow and server costly code.

Method Range.getValue is heavily used

The Good

Continue reading “Google Apps Script – Iterating Through Ranges in Sheets the Right and Wrong Way”