Formatting your spreadsheets automatically

When looking at the Spreadsheet Service documentation (https://developers.google.com/apps-script/reference/spreadsheet/) and particularly the Sheet class (https://developers.google.com/apps-script/reference/spreadsheet/sheet) and the Range class (https://developers.google.com/apps-script/reference/spreadsheet/range), we can see that there are a lot of available methods that allow us to modify the sheet format; in other words, we can create functions that will set up a custom page layout with a single click.

This could be very helpful when creating shared documents with people who have to fill in some data and have a bad habit of making a mess of the page layout.

For example, they might have to change the column width and sorting, background color, or even the font size and family.

It can become a real pain to check for that all the time and reset it the way you want by proceeding step-by-step in the spreadsheet user interface (this has been a live experience for me many times as I have a few, very undisciplined collaborators).

Let us wipe out the code we used so far and try the following new one:

function resetPageLayout() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  ss.toast('Now processing your sheet','Wait a few seconds',5);
  var header1 = sh.getRange('A1:G1').mergeAcross().setBackground('silver').setValue('Party menu suggestion');
  var header2 = sh.getRange(2,1,1,7).setBackground('#aaaaff').setValues([['First Name','Last Name','Drink','Softs','Appetizers','Meal','Dessert']]);
  sh.getRange(1,1,2,7).setBorder(true,true,true,true,true,true).setHorizontalAlignment('center').setVerticalAlignment('middle').setFontWeight('bold').setFontSize(14);
  var columnWidth = [150,150,180,180,180,300,200];
  for(var n=0; n < columnWidth.length ; n++){
    sh.setColumnWidth(n+1,columnWidth[n]);
  }
  sh.insertColumnAfter(7).deleteColumns(8,sh.getMaxColumns()-7);
  sh.insertRows(sh.getLastRow()+1,20);
  sh.deleteRows(sh.getLastRow()+1, sh.getMaxRows()-sh.getLastRow()-10);
  sh.getRange(3,1,sh.getMaxRows()-2,sh.getLastColumn()).setBorder(false, true, false, true, true, false);// top, left, bottom, right, vertical, horizontal
  for(var n=sh.getLastRow() ; n > 3 ; n--){
    Logger.log(n+'  '+sh.getRange(n,1,1,7).getValues())
    if(sh.getRange(n,1,1,7).getValues().toString().replace(/,/g,'')==''){
      sh.deleteRow(n);
      Logger.log('row '+n+' deleted');
    }
  }
  sh.setFrozenRows(2);
  SpreadsheetApp.flush();
  Browser.msgBox('Now your sheet should be clean again !');
}

Be sure you have nothing in your sheet that you would like to save as it might be deleted when this function will execute.

Note

If your spreadsheet settings are in a language other than English, you might need to edit the sheet name either in the script (in the third line of code) or in your spreadsheet document itself by clicking on the name at the bottom of the page.

Now you can run the code from the script editor and you will get the following pop up requesting your authorization for this script to run:

When clicking on Continue, you will see a second pop up with the actual authorization process along with the details of every service used by the script. In this particular case, the only service the script is using is Spreadsheet Service as we can see in the following screenshot:

Once you accept the authorization request, the script will continue to execute and you can go back to your spreadsheet to see it working live!

The following screenshot shows the formatted spreadsheet:

You will probably notice in the preceding screenshot that the page suggests to add 1000 rows. That is because I am using the new spreadsheet in this test. If you are still using the current standard version the number will be 20. Everything is growing with time!

Note

At the time of writing this (December 2013), there are still a few issues with the new spreadsheet and some of the scripts described in this book are not yet available. This will be clearly highlighted when it occurs.

For up-to-date information on this, the best source is the drive support web page that is full of interesting information (https://support.google.com/drive/answer/3541068?hl=en&ref_topic=20322).

I have to admit that it takes some time to write such a script and that its use case is quite limited, but I chose this example to show a sample of the very broad panel of possibilities Google Apps Script offers and to introduce the next section in this chapter.

Menus and custom buttons in spreadsheets

Running a script from the editor might quickly become annoying as it forces us to switch between two tabs or browser windows.

That's a good opportunity to look at the menu customization and the embedded buttons.

Let's start with creating a menu by executing the following code:

function createMenu(){
  var menuEntries = [ {name: "resetPageLayout", functionName:"resetPageLayout"}];
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  sh.addMenu("Format utilities",menuEntries);
}

Note

If you are using the new spreadsheet version and as described in the release note published on January 21, 2014 (https://developers.google.com/apps-script/releases/#january_2014), a new method is available to create custom menus (the old method is still usable).

The syntax is quite different, the following code shows the new version:

function createMenu_new() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu ('Format utilities');
  menu.addItem ('resetPageLayout','resetPageLayout');
  menu.addToUi();
}

These few lines will create a custom menu in your spreadsheet as shown in the following screenshot:

When you click on the available item, the function is called; you don't even need to open the script editor anymore.

The problem with the preceding code is that the menu will disappear when you close the document.

When you open it again, you will have to reopen the editor and run the createMenu function again. To get rid of that annoying task, we will use a special function named onOpen.

The function onOpen will execute automatically when anyone opens the spreadsheet; we'll see later that there are other such special functions that execute automatically when some event occurs.

The preceding code needs very few modifications to run automatically; just change the function name from createMenu()to onOpen().

After refreshing your browser window, you will see the new menu appear right between Tools and Help. A new bit of magic!

The other option is to include a custom button in your spreadsheet to trigger a script function you created.

From the spreadsheet menu, go to Insert Drawing and use the toolset to draw a button the way you like; save and close the drawing editor and your new button will be waiting for you in the spreadsheet as shown in the following screenshot:

If you click on the New button, you'll see a small menu that will allow you to assign a script function to that button as shown in the following screenshot:

Type the name of the function you want to use as shown in the following screenshot:

You're done! Place it where you want and enjoy.

You can use the same procedure to insert a figure that you will be able to scale to your needs and assign a function to.

Note

This feature (the script assignment of both drawings and figures) is not yet available in the new version of spreadsheets.

This is a pleasant feature that makes spreadsheet script comfortable to use but there are a few limitations that are listed as follows:

• The position of figures and drawings is fixed on the sheet and not on the screen; that is, when you scroll down a long list of data, the figure might disappear from the view area

• Figures and drawings cannot be inserted in the frozen rows or frozen columns area

• They can be changed, moved, or deleted by any user who has editing rights to the spreadsheet so it's not necessarily a good idea to use them in shared documents

The script from this example is probably a bad example of button or custom menu use, as both will be available to every user who can edit the document; your friends might be surprised by its effects but this was, of course, just an example.

We'll see in the next part that this example script is not ideally written as far as speed and efficiency are concerned.