copy

Tuesday, 30 October 2018

Last updated: 07/11/2018, 2 min read (without the code)

Creating & Customizing Column Charts In Google Sheets



Introduction



Google Sheets, much like Microsoft Excel, incorporate many different chart types, which can help the user to visualize his/her data. In this post, we will learn how to create and customize a simple column chart in Google Sheets. There will be the “manual way”, the “coding way” – using Google Apps Script – as well a bonus tip for selecting chart colors.



The manual way



To create a column chart manually just follow these 2 simple steps:

Step 1: First, select the range containing the data, e.g. A1:C4.

Range Selection

Step 2: Then, go to the menu Insert and click the Chart option.

Insert Chart

The generated chart would look like this:

Default Column Chart

To customize the chart, right-click upon the chart, and on the context menu that pop-ups, select the appropriate option. For example, to customize the chart title, you have to select the Chart & axis titles option and then the Chart title option.

Customizing Chart Title

The Chart editor form appears, where you can customize several options. Here, for example, we have changed the text, font, font size, format and the text color of the title.

Chart Editor

In a similar way, you can customize every available chart option (e.g. series, legend, axes and gridlines).



The coding way



The manual way can be quite daunting and time-consuming, especially if you have to customize several chart options. Thanks to Apps Script, though, the same process can be automated, allowing you to create beautiful charts in a few seconds. The code below contains probably the most common options that someone will need to customize when creating a column chart. It has the following options:
  • Generic chart options: chart position and size.
  • Chart title options: text and text style.
  • Legend options: position and text style.
  • X-axis options: title and text style.
  • Y-axis options: title, gridlines and text style.
  • Series options: color, data labels, error bars and text style.
  • Trendline options: type and line style.
Most of these options are set using mainly the setOption method of the chart object. The various options are passed using a CSS syntax. Additional options can be found on this page.

function createColumnChart() {

  /*
  ------------------------------------------------------------------------------
  The function creates a custom column chart in the active sheet using the data
  that exist in the range that is specified in the dataRange variable.
  
  Written By:    Christos Samaras
  Date:          30/10/2018
  Last Updated:  07/11/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  ------------------------------------------------------------------------------
  */
  
  // Range to get the data.
  var dataRange = 'A1:C4';
  
  // Variables for customizing colors.
  // Use this tool to generate "good" colors:
  // http://paletton.com
  var color1 = '#EE295A';   // Fill and data label color for the first series.
  var color2 = '#FFD42C';   // Fill and data label color for the second series.
  var color3 = '#8D7CEE';   // Trendline and axis titles color.
  var color4 = '#A6F870';   // Gridlines, axis labels and chart title color.
  
  // Text for titles (chart and both axes).
  var chartTitle = 'Vehicles Comparisons';
  var xAxisTitle = 'Vehicle Category';
  var yAxisTitle = 'Speed & Power';
  
  // Get the active sheet.
  var spreadsheet = SpreadsheetApp.getActive();  
  var sheet = spreadsheet.getActiveSheet();
  
  // Create a chart.
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(spreadsheet.getRange(dataRange))
  
  // Set the generic options.
  .setNumHeaders(1)
  .setBackgroundColor('#333333')  
  //  .setOption('is3D', true)   // For a 3D chart.
  //  .setOption('height', 400)  // Custom height. 
  //  .setOption('width', 600)   // Custom width.
  
  // Set the chart title options.
  .setOption('title', chartTitle)
  .setOption('titleTextStyle', {
    color: color4,    
    alignment: 'center',
    fontName: 'Verdana',
    fontSize: 24,    
    bold: true    
  })
  
  // Set the legend options.
  .setOption('legend', {
    position: 'top',
    alignment: 'center',
    textStyle: {
      color: color3,
      fontName: 'Verdana',
      fontSize: 12,
      bold: true
    }
  })
  
  // Set the X-axis options.
  .setOption('hAxis', {
    title: xAxisTitle,
    titleTextStyle: {
      color: color3,
      alignment: 'center',
      fontName: 'Verdana',
      fontSize: 16,
      bold: true
    },
    textStyle: {
      color: color4,
      fontName: 'Verdana',
      fontSize: 14,
      bold: true
    }
  })

  // Set the Y-axis options.
  .setOption('vAxes', {
    0: {
      title: yAxisTitle,
      format: 'none',
      titleTextStyle: {
        color: color3,
        alignment: 'center',
        fontName: 'Verdana',
        fontSize: 16,
        bold: true
      },
      textStyle: {
        color: color4,
        fontName: 'Verdana',
        fontSize: 14,
        bold: true
      },
      gridlines: {
        count: -1,
        color: color4,
        width: 5
      }
    }
  })

  // Set the series options.
  .setOption("series", {
    0: {
      labelInLegend: 'Speed [km/h]',
      color: color1,      
      hasAnnotations: true,
      dataLabel: 'value',
      dataLabelPlacement: 'outsideEnd',
      errorBars: {
        magnitude: 20,
        errorType: "percent"
      },
      dataLabel: "value",
      textStyle: {
        color: color1,
        fontSize: 12,
        fontName: 'Verdana',
        bold: true
      }
    },
    1: {
      labelInLegend: 'Power [kW]',
      color: color2,
      hasAnnotations: true,
      dataLabel: 'value',
      dataLabelPlacement: 'outsideEnd',
      errorBars: {
        magnitude: 20,
        errorType: "percent"
      },
      dataLabel: "value",
      textStyle: {
        color: color2,
        fontSize: 12,
        fontName: 'Verdana',
        bold: true
      }
    }
  })
  
  // Set the trendline options (here only for the first series).
  .setOption('trendlines', {
    0: {
      color: color3,
      type: 'linear',
      lineWidth: 4,
      opacity: 0.8,
      labelInLegend: 'Trendline',
      visibleInLegend: true
    }
  })
  
  // Position: row and column of top left corner (cell E2 here).
  // Optional: offset X and Y in pixels.
  .setPosition(2, 5, 0, 0)
  
  // Finally, build the chart.
  .build();
  sheet.insertChart(chart);
};

If you run the above script on the same dataset (A1:C4), the final chart will look like this:

Column Chart With Code



How to select “good” chart colors



If you saw the chart that was generated from the previous Apps Script code you would probably notice that the color combination is quite “pleasing to the eye”.  The particular set of colors was selected using this tool. If you play with it for a few minutes, you will discover that it has an extensive set of options for generating color palettes and harmonies. If you don’t know where to start, simply click the randomize button at the top of the screen to start your color scheme.

Palleton

Unlike Excel, the chart colors in Google Sheets can be customized more extensively. Therefore, you can create more impressive charts if you are willing to experiment a little bit with the color combinations.

Sunday, 30 September 2018

Last updated: 05/10/2018, 3 min read (without the code)

Triggers & Events In Google Sheets


Introduction



According to the documentation, triggers let Google Apps Script run a function automatically when a certain event, like opening a document, occurs. Apps Script supports two types of triggers, simple and installable. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which is executed when a user opens a Google Docs, Sheets, Slides, or Forms file. Installable triggers, on the other hand, offer more capabilities than simple triggers but must be activated before use. They can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

Both types of triggers let Apps Script run a function automatically if a certain event occurs.  When a trigger fires, Apps Script passes in the function an event object as an argument, typically called “e”. The event object contains information about the context that caused the trigger to fire. For example, the simple onEdit(e) trigger for a Google Sheets script that we will see below, uses the event object to determine which cell was edited.

Unlike VBA, in Google Apps Script there are only a few events that can be used to detect changes within an application. In this post, we will analyze two of the most useful ones, the onEdit and the onOpen. Examples will be given on Google Sheets application. We will also learn how to install a trigger, both manually and programmatically.



GAS code



In the code below there are seven GAS functions:
  • onEdit: A simple trigger that fires when the contents of a cell change. In the particular case, the range that is checked for changes is the "A1:A10" in the active sheet.
  • onOpen: A simple trigger that is actually a logger, which runs every time that someone opens the spreadsheet. It fills the current date/time in the last row of column B in the active sheet.
  • createSpreadsheetOpenTrigger: It creates an open trigger programmatically.
  • createTimeDrivenTrigger: It creates a time-driven trigger programmatically that runs every 5 minutes.
  • rangeIntersect: A helping function that returns true when two ranges intersect. This is the GAS implementation of the Application.Intersect method that exists in VBA/Excel.
  • greeting: A function that displays a message box in the browser.
  • myCounter: A function that finds the last row in column D of the active sheet and adds +1 to the previous value.

/*
  --------------------------------------------------------------------------------
  The next set of functions show how to use triggers and events in Google Sheets.

  Written By:    Christos Samaras
  Date:          29/09/2018
  Last Updated:  05/10/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  --------------------------------------------------------------------------------
*/

/*
  ----------------
  Simple Triggers
  ----------------
*/
function onEdit(e)
{  
  /*
  -----------------------------------------------------------------------------------------
  A simple trigger that fires after a cell change in the range A1:A10 of the active sheet.
  -----------------------------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Set the range that has to be chekced for changes.
  var selectedRange = sheet.getRange("A1:A10");
  
  // Get the active cell from the event obejct.
  var activeCell = e.range;
  
  // Check if the active cell belongs to the range of interest. 
  // If yes, show a message box in the browser.
  if(rangeIntersect(activeCell, selectedRange)) 
    Browser.msgBox("Triggered after the cell " + activeCell.getA1Notation() + " changed!");   
}

function onOpen(e)
{
  /*
  -------------------------------------------------------------------
  A simple trigger that acts as a logger, filling the date/time that
  the spreadsheet was openned in the last row of the column B.
  -------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column B that contains values.
  var lastRow = sheet.getRange("B1:B").getValues().filter(String).length;
  
  // Set the current date/time in the next available row in column B.
  sheet.getRange(lastRow + 1, 2).setValue((new Date()).toLocaleString());  
}


/*
  ---------------------
  Installable Triggers
  ---------------------
*/
function createSpreadsheetOpenTrigger() 
{
  /*
  --------------------------------------------------------------------------
  Creates a trigger programmatically that fires when the spreadsheet opens.
  --------------------------------------------------------------------------
  */
  
  // Get the active spreadsheet (workbook in Excel terminology).
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Create a new trigger that will run when the spreadsheet opens.
  // The trigger will fire the greeting function.
  ScriptApp.newTrigger('greeting').forSpreadsheet(ss).onOpen().create();
}

function createTimeDrivenTrigger() 
{    
  /*
  ---------------------------------------------------------------
  Creates a trigger programmatically that fires every 5 minutes.
  ---------------------------------------------------------------
  */
  
  // Create a new trigger that will run every 5 minutes.
  // The trigger will run the myCounter function.
  ScriptApp.newTrigger('myCounter').timeBased().everyMinutes(5).create();
}


/*
  ------------------
  Helping Functions
  ------------------
*/
function rangeIntersect (rng1, rng2) 
{
  /*
  ------------------------------------------
  Returns true if the two ranges intersect.
  ------------------------------------------
  */
 
  // Check for empty objects.
  if(rng1 == null || rng2 == null)
    return false;
  
  // Compare the rows and columns of the two ranges.
  return (rng1.getLastRow() >= rng2.getRow()) && (rng2.getLastRow() >= rng1.getRow()) 
          && (rng1.getLastColumn() >= rng2.getColumn()) && (rng2.getLastColumn() >= rng1.getColumn());
}

function greeting()
{
  /*
  ---------------------------------
  It displays a hello message box.
  ---------------------------------
  */ 
  
  // Display hello in the browser.
  Browser.msgBox("Hello!");
}

function myCounter()
{
  /*
  ----------------------------------------------------------------
  It adds +1 in the last row of the column D of the active sheet.
  ----------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column D that contains values.  
  var lastRow = sheet.getRange("D1:D").getValues().filter(String).length;

  // Check if the last row is 0. If yes, fill the number 1 in the cell D1.
  if(lastRow == 0)
  {
    sheet.getRange(lastRow + 1, 4).setValue(1);
    return;
  }
  
  // Add +1 in the next available row in column D.
  sheet.getRange(lastRow + 1, 4).setValue(sheet.getRange(lastRow, 4).getValue() + 1);
}

onEdit example

OnEdit Event Example

The above image shows what happens when the onEdit trigger runs. If the user changes the contents of the cell A6, a message box will pop-up in the browser. Of course, this simple event was written only for demonstration purposes.


onOpen example

OnOpen Event Example

When the user double-clicks to open the selected spreadsheet, the current date/time will be filled in the last row of column B of the active sheet. This event can be very useful for logging purposes.



How to install a trigger



The manual way

To manually install a trigger, you will have to go to the Script editor. There, from the menu, select Edit and then Current's project triggers.

Current Project Triggers

In the window that will pop up, click on the No triggers set up. Click here to add one now. hyperlink.

Add New Trigger
In the form that pop-ups, select the function you want to run and the appropriate event. In this example, the greeting function was selected from the dropdown list, along with the From spreadsheet and On open event. To save the trigger, click on the Save button. That's it!
Set An Open Trigger


The programmatic way

The programmatic way is probably as easy as the manual one. You just have to use the createSpreadsheetOpenTrigger function and adjust it to your needs (e.g. change the function that will be executed when the spreadsheet opens).

To set the trigger, simply click the Run option on the menu of Script editor and select the createSpreadsheetOpenTrigger function from the sub-menu. The function will run and the trigger will be set.

Run Function On GAS Editor


Authorization procedure

Note: the first time that you will try to install a trigger, either manually or programmatically, you will have to set authorization permissions. So, a message box will pop-up asking you to authorize the trigger. Just click the Review Permissions button.

Trigger Authorization

Select your Google account to continue.

Select Google Account

Finally, in the next form, click on the Allow button.

Allow Access To Google Account

Congratulations! You have successfully installed a trigger! Read here about the restrictions that installable triggers have.



Read also



Tuesday, 28 August 2018

Last updated: 28/08/2018, 1 min read (without the code)

Geocoding & Reverse Geocoding Functions In Google Sheets


Introduction



In the previous post, we learned the basics about developing and using a custom function in Google Sheets. It is time now to switch to something more advanced compared to our first example. We have seen in the past how to use Google’s geocoding services from VBA. Here we will see how to call these services using Google Apps Script (GAS). The main advantage when developing a GAS function is that, unlike VBA, you don’t need to explicitly get an API key. The overall approach is slightly different than in the case of VBA since there is a Geocoder class available in GAS.



GAS code



Below you will find the code for five different GAS functions:
  • getCoordinates
  • getCoordinatesArray
  • getLatitude
  • getLongitude
  • getAddress
I think that the function names are self-explanatory. It should be noted that the getCoordinatesArray is identical to getCoordinates, but it returns the latitude and longitude as an array, not as a single string. In addition, the getAddress function is performing actually reverse geocoding. The idea behind these functions is similar to VBA, so after reading the request status, then if it is "OK", the corresponding node is read and returned.

/*
  -------------------------------------------------------------------------------
  The next set of functions are used to perform geocoding and reverse geocoding.

  Written By:    Christos Samaras
  Date:          18/08/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  -------------------------------------------------------------------------------
*/

/**
 * This function returns the latitude and longitude of a given address using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinates(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as string.  
  return lat + ", " + long;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the latitude and longitude of a given address as an array using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinatesArray(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as an array.  
  return [lat, long];
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the latitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude of the given address.
 * @customfunction
 */
function getLatitude(address)
{        
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitude variable.
  var lat = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];      
  
  // Return the latitude as double.
  return lat;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the longitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The longitude of the given address.
 * @customfunction
 */
function getLongitude(address)
{    
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the longitude variable.
  var long = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  
  // Return the longitude as double.  
  return long;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the address from a given pair of latitude and longitude using the Geocoder class.
 * In other words, it performs reverse geocoding.
 *
 * @param {A2} lat A cell that contains a latitude value between -90 and +90 degrees.
 * @param {B2} long A cell that contains a longitude value between -180 and +180 degrees.
 * @return The address from a given pair of latitude and longitude.
 * @customfunction
 */
 function getAddress(lat, long)
{
  
  // Checking the input variables:
  // The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.   
  if(lat < -90 || lat > 90)
    return "Invalid Latitude";

  // Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
  if(long < -180 || long > 180)
    return "Invalid Longitude";
    
  var response = Maps.newGeocoder().reverseGeocode(lat, long);
  var address = "";
  
  if (response.status == 'OK')
    address = response["results"][0]["formatted_address"];
  
  return address;
}

All functions containing descriptions following the approach that was analyzed here. The getCoordinatesArray function, though, needs "special treatment" to get its results. Here is how you can call it:
a. Select two adjacent cells.
b. In one of the two cells, enter the formula:
 =TRANSPOSE(ARRAYFORMULA(getCoordinatesArray(A5))) 
Where A5 is the cell containing the address you need to geocode.

NOTE: In the case of multiple results (for example two cities sharing the same name), the above functions return the first occurrence, so be careful with your input.



Online examples



You can find a few examples online in this Google Sheets file.



Read also



How To Create & Use A Custom Function In Google Sheets

Sunday, 19 August 2018

Last updated:08/09/2018, 5 min read

How To Create & Use A Custom Function In Google Sheets


Introduction



Google Sheets, along with Google Docs and Google Slides are a spreadsheet, a word processor, and a presentation program respectively, all part of a free, web-based software office suite offered by Google within its Google Drive service. The three applications are available as web apps, mobile apps and desktop apps (on ChromeOS only). The applications are compatible with the corresponding Microsoft Office file formats, therefore, the user can save an “online file” to his desktop and then open it with the appropriate Office app.

To be more precise, the Google Sheets app is very similar to Excel. If you have ever used Excel, especially some pre-Ribbon version (Office 2003 and back), you will definitely understand how the Google Sheets app works. The similarities don’t stop only on the environment and the user interface. The Google Sheets app contains an internal script editor where the user can create his/her own custom functions, similar to VBA functions in Excel. The language that is used to create these custom functions in Google Sheets is called Apps Script. The Apps Script is also used in other Google apps, so it reminds a lot of the universal usage of VBA in the entire Office suite.

The Apps Script is actually the Google’s version of ECMAScript, which runs on its servers (not in the browser). Consequently, Apps Script can be considered as a browser-independent language. If you haven’t heard the ECMAScript in the past, don’t worry! You probably already know another “dialect” of it, called JavaScript. In short, Apps Script is based on JavaScript 1.6 with some portions of 1.7 and 1.8 and provides a subset of ECMAScript 5 API. According to Google, the Apps Script "provides easy ways to automate tasks across Google products and third party services”. This is the language that we will use in this tutorial.



Create a new spreadsheet on Google Sheets



Before we write our first custom function using Apps Script, we will learn first how to create a new blank spreadsheet on Google Sheets. So, please follow the next simple steps:

Step 1: First of all, ensure that you are logged in to your Google account.

Log In To Google Account

Step 2: Go to the Google Drive page.

Logged To Google Drive

Step 3: In this page, you should click on the New button. Then, on the drop-down menu select the Google Sheets option by clicking the small arrow. Finally, click on the Blank spreadsheet option.

Create A New Spreadsheet In Google Sheets

Step 4: A new spreadsheet is created and you are redirected to a new page. To rename and this spreadsheet, click on the Untitled spreadsheet title and enter your preferred name.

Rename The Spreadsheet

If you hit enter, the spreadsheet will look like the image below.

Renamed Spreadsheet

Note, that all the changes are automatically saved in Google Drive, so unlike Excel, you don’t have to manually save it. Here is what you will see on your Google Drive:

Renamed File On Google Drive



The Apps Script editor



Now that we have created and renamed our spreadsheet, we are ready to switch to our Apps Script editor. Select the Tools category from the menu and then click on the Script editor option.

Switch To Script Editor

A new page pop-up; that’s our Apps Script editor or if you prefer our Integrated Development Environment (IDE). Unlike the VBA IDE, it is relatively simpler, nevertheless, it includes sufficient functionality.

The Script Editor

The default project contains a single script file (Code.gs) and an empty function (myFunction), which we will modify in a little bit. Script files are similar to VBA modules. Multiple script files can be created and organized inside a single project (i.e. as it is in the case of a VB 6.0 project). Within each script file, you can write code that is accessible from all the script files included in the project. Unlike VBA, there is no option to restrict access using the Option Private Module.

If you need to rename the Untitled project, simply click on the title. An input box will pop-up, prompting you to give a name. Type your preferred name and click the OK button.

Rename The Project
Here is how the renamed project would look like.

The Renamed Apps Script Project



Creating your first Apps Script function



In this point, it should be highlighted that the custom function will be created inside a container-bound script that is part of our spreadsheet. The container-bound functions are equivalent to VBA functions that are part of an Excel spreadsheet. Bear in mind, though, that there are also standalone scripts, which are created on Google Drive and usually contain generic functionality that can be used across multiple Google apps. In other words, these scripts are not bound to a specific spreadsheet or document. However, we will talk about this kind of functions and scripts in a different tutorial.

Our first function will calculate the area of a circle, given its radius. The formula is very simple:
Area = π ∙ Radius²

Here is the complete code for the custom function:

/**
 * This function returns the area of a circle given its radius. 
 *
 * @param {A1} radius A cell that contains the circle radius.
 * @return The area of the circle given its radius.
 * @customfunction
 */
function circleArea(radius) {
  return Math.PI * Math.pow(radius, 2);
} 

Let’s analyze the code a little bit:
  • The circleArea is the name of the function that will be used from the spreadsheet. Here we followed the typical camel case notation (e.g. the practice of writing compound words or phrases such that each word or abbreviation in the middle of the phrase begins with a capital letter, with no intervening spaces or punctuation).
  • The radius is our input variable. Note that, unlike VBA, we don’t have to declare the variable’s type (e.g. double) since, in Apps Script, all the variables are of type var.
  • The code that starts with the return word is actually the mathematical formula translated into code. Ιn Apps Script, similar to JavaScript, we have to use an internal library called Math for some common mathematical expressions (as it is in the case of power and the constant pi - π).
  • Finally, the comments that are enclosed between “/*” and “*/” are optional, but very helpful since will be used by the Autocomplete feature, which we will see below.
Your first custom function is almost ready! As you can see in the image below, an asterisk appears next to the Code.gs file. Click the Save button or use the CTRL + S shortcut from your keyboard.

Custom Function Before Saving

Congratulations! You just created your first custom Apps Script function. Here is how the saved function would look like. The asterisk is gone and the function name will appear in the dropdown menu that contains the functions.

Custom Function Saved



Use the custom Apps Script function from the spreadsheet



You can now close the Apps Script editor and go back to the spreadsheet. In a random cell (e.g. B1) start typing the function name (=circleArea). If you just type =ci notice that the Autocomplete feature appears, showing the function description.

Typing The Function Name

If you click with the mouse upon the Autocomplete window, or simply press the Tab key, you will see the full function description. This is the reason why it is a good practice to fill the function description in the script editor.

Full Function Description

If you type =circleArea(5) and click the Enter, you will see a Loading message and after a few seconds, the actual result in the cell (78.53981634).

Custom Function With Value

Of course, apart from manual values, we can use the function with cell references. In the example below, we use the function with the cell A4.

Custom Function With Cell Reference

Well done! You have successfully created and used your first custom Apps Script function. More tutorials on Apps Script will follow soon. Meanwhile, if you need more information about the custom Apps Script functions, you can visit the official page.



Read also



Geocoding & Reverse Geocoding Functions In Google Sheets

Sunday, 15 July 2018

Last updated: 15/07/2018, 2 min read (without the code)

Get & Set The Default Windows Printer With VBA


Introduction



Although VBA is a great language for building Office “applications”, when it comes to handling Windows devices, such as a printer, for example, things start to become difficult. The obvious reason is that the VBA was not designed for this kind of purposes. However, what if your VBA “application” needs to know how many printers are installed and available in the particular computer? What if your “application” must set the default Windows printer to “Adobe PDF”, hence, printing in a PDF file, instead of a paper?

To answer the last two questions, somebody might think to search for some old Visual Basic 6.0 code snippets. In other words, he/she might try to find solutions based on some old example. While this totally OK, the VB 6.0 solutions will probably rely on one or more Windows APIs. So, if you are not very familiar using Windows APIs in your VBA code, you might have troubles adjusting the API calls. The latter is particularly true when the Office version in which your “application” will run, is 64bit. In that case, you need to carefully "alter" the API calls so as to work in 64bit (e.g. data type conversion: the Long should become LongPtr in 64bit).

But, are there any simpler solutions? Yes, there are! If we combine the VBA with Windows Management Instrumentation (WMI) and Windows Script Host (WSH) objects we can do wonders quite easily! The VBA module that follows demonstrates several techniques that show: how somebody can get the installed printers from a computer, how to check if a printer is the default one, and, finally, how to set a particular printer to be the default one.

Note: the term “application” in the above paragraphs actually implies a solution to a given problem (e.g. a budget spreadsheet), not an application with the strict definition of the term (e.g. an executable). You can develop “real” applications using VB 6.0, as well as with other programming languages, but not with VBA.



VBA code



The code below is an entire module that contains 3 VBA functions:
  • PrinterExists: A function that checks if there is a printer installed with the given name.
  • IsDefaultPrinter: A function that checks if the given printer corresponds to the default Windows printer.
  • SetDefaultPrinter: A functions that set the given printer to be the default Windows printer.
   
Next, there are 2 macros that demonstrate how these 3 functions can be used/combined to do something useful:
  • GetInstalledPrinters: A macro that loops through all the installed printers of the computer and writes their names in the "Printers" worksheet. Moreover, it checks if each printer is the default one or not.
  • SetAsTheDefaultPrinter: A macro that sets the selected range, if it corresponds to an installed printer, to be the default Windows printer. The user must select a range within the given range of (valid) printers, and, then, run the macro.

Option Explicit
        
    '-------------------------------------------------------------------------------------------------------------------------
    'This module contains 3 functions that can help you whenever you deal with printers from VBA:
    '- PrinterExists:           Checks if there is a printer installed with the given name.
    '- IsDefaultPrinter:        Checks if the given printer corresponds to the default windows printer.
    '- SetDefaultPrinter:       Makes the given printer to be the default one.
    '
    'After these functions, there are 2 macros that demonstrate how these functions can be used to do something useful.
    'Note that the macros were adjusted to work with the specific workbook that contains the worksheet named "Printers".
    '- GetInstalledPrinters:    Loops through all the installed printers and writes their names in the "Printers" worksheet.
    '                           Moreover, it checks if each printer is the default one.
    '
    '- SetAsTheDefaultPrinter:  The user selects a range within the given range of printers and then by running the macro
    '                           the selected printer becomes the default one.
    '
    'Written By:    Christos Samaras
    'Date:          14/08/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '-------------------------------------------------------------------------------------------------------------------------
    
Function PrinterExists(printerName As String) As Boolean
    
    'Declaring the necessary variables.
    Dim computer            As String
    Dim wmiService          As Object
    Dim installedPrinters   As Variant
    Dim printer             As Object
    
    On Error Resume Next
    
    'Check if the printer name is empty.
    If printerName = vbNullString Then Exit Function

    'Set the computer. Dot means the computer running the code.    
    computer = "."
    
    'Get the WMI object
    Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
    
    'Retrieve information about the installed printers (by running a query).
    Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
        
    'If an error occurs in the previous step, the function should exit and return False.
    If Err.Number <> 0 Then Exit Function

    'Loop through all the installed printers. If the given name matches to any of the installed printers, exit the loop and return True.                      
    For Each printer In installedPrinters
        If UCase(printer.Name) = UCase(printerName) Then
            PrinterExists = True
            Exit Function
        End If
    Next printer
    
    On Error GoTo 0
    
End Function

Function IsDefaultPrinter(printerName As String) As Boolean
      
    'Declaring the necessary variables.
    Dim computer            As String
    Dim wmiService          As Object
    Dim installedPrinters   As Variant
    Dim printer             As Object
    
    On Error Resume Next
    
    'Check if the printer name is empty.
    If printerName = vbNullString Then Exit Function
    
    'Set the computer. Dot means the computer running the code.    
    computer = "."
    
    'Get the WMI object
    Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
    
    'Retrieve information about the installed printers (by running a query).
    Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
        
    'If an error occurs in the previous step, the function should exit and return False.
    If Err.Number <> 0 Then Exit Function

    'Loop through all the installed printers. If the given name matches to any of the installed printers                
    'and the Default property is set to True, exit the loop and return True.
    For Each printer In installedPrinters
        If UCase(printer.Name) = UCase(printerName) And printer.Default = True Then
            IsDefaultPrinter = True
            Exit Function
        End If
    Next printer
    
    On Error GoTo 0
    
End Function

Function SetDefaultPrinter(printerName As String) As Boolean
    
    'Declaring the necessary variable.
    Dim wshNetwork As Object
    
    On Error Resume Next
    
    'Check if the printer name is empty.
    If printerName = vbNullString Then Exit Function

    'Test if the printer is already the default one. If yes, return True.    
    If IsDefaultPrinter(printerName) = True Then
        SetDefaultPrinter = True
        Exit Function
    End If
        
    'The printer is not the default one. Create the WScript.Network object.
    Set wshNetwork = CreateObject("WScript.Network")
    
    'If the WScript.Network object was not created, exit.
    If wshNetwork Is Nothing Then Exit Function

    'Set the given printer to be the default one.    
    wshNetwork.SetDefaultPrinter printerName
                
    'Release the WScript.Network object.
    Set wshNetwork = Nothing
    
    'Check (again) if after the change, the given printer is indeed the default one.
    SetDefaultPrinter = IsDefaultPrinter(printerName)
    
    On Error GoTo 0
    
End Function

Sub GetInstalledPrinters()
      
    'Declaring the necessary variables.
    Dim sht                 As Worksheet
    Dim computer            As String
    Dim wmiService          As Object
    Dim installedPrinters   As Variant
    Dim printer             As Object
    Dim i                   As Integer
    
    On Error Resume Next
    
    'Set the worksheet in which the information will be written.
    Set sht = ThisWorkbook.Worksheets("Printers")
    
    'Check if the sheet exist (there is no error).
    If Err.Number <> 0 Then
        MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
        Exit Sub
    End If
    
    'Clear existing data.
    Call ClearAll
    
    'Set the computer. Dot means the computer running the code.    
    computer = "."
    
    'Get the WMI object
    Set wmiService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & computer & "\root\cimv2")
    
    'Retrieve information about the installed printers (by running a query).
    Set installedPrinters = wmiService.ExecQuery("Select * from Win32_Printer")
        
    'If an error occurs in the previous step, inform the user.
    If Err.Number <> 0 Then
        MsgBox "Could not retrieve the printer information from WMI object!", vbCritical, "WMI Object Error"
        Exit Sub
    End If
              
    'Set the starting row.
    i = 5
     
    'Loop through all the installed printers and get their name. Check if one of them is the default one.
    For Each printer In installedPrinters
        
        'Write the results to the worksheet.
        sht.Range("C" & i).Value = printer.Name
        sht.Range("D" & i).Value = printer.Default
        i = i + 1
        
    Next printer
    
    On Error GoTo 0

End Sub

Sub SetAsTheDefaultPrinter()
    
    'Declaring the necessary variable.
    Dim sht     As Worksheet
    Dim rng     As Range
    
    On Error Resume Next
    
    'Set the worksheet in which the information will be written.
    Set sht = ThisWorkbook.Worksheets("Printers")
    
    'Check if the sheet exist (there is no error).
    If Err.Number <> 0 Then
        MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
        Exit Sub
    End If
    
    'Get the intersected range.
    Set rng = Application.Intersect(sht.Range("C5:C24"), Selection.Range("A1"))
    
    'If there is no "common" range, exit.
    If rng Is Nothing Then
        MsgBox "The selected range is outside the 'C5:C24' range!", vbCritical, "Invalid Common Range Error"
        Exit Sub
    End If
    
    'If the common range is empty, exit.
    If IsEmpty(rng) Then
        MsgBox "The range you selected is empty!", vbCritical, "Empty Range Error"
        Exit Sub
    End If
    
    'Check if the selected printer is already the default printer.
    If IsDefaultPrinter(rng.Range("A1")) Then
        MsgBox "The selected printer '" & rng.Range("A1") & "' is already the default printer!", vbExclamation, "Default Printer Warning"
        Exit Sub
    End If
    
    'Finally, set the selected printer as the default one and inform the user.
    If SetDefaultPrinter(rng.Range("A1")) = True Then
        
        'Run the GetInstalledPrinters macro to "prove" the change.
        Call GetInstalledPrinters
        
        'The process succeded.
        MsgBox "The selected printer '" & rng.Range("A1") & "' was set as the default printer!", vbInformation, "Success"
        
    Else
    
        'The process failed.
        MsgBox "It was impossible to set the selected printer '" & rng.Range("A1") & "' as the default printer!", vbCritical, "Failure"
        
    End If

End Sub

Sub ClearAll()
      
    'Declaring the necessary variable.
    Dim sht As Worksheet
    
    On Error Resume Next
    
    'Set the worksheet in which the information will be written.
    Set sht = ThisWorkbook.Worksheets("Printers")
    
    'Check if the sheet exist (there is no error).
    If Err.Number <> 0 Then
        MsgBox "The sheet does not exists!", vbCritical, "Sheet Name Error"
        Exit Sub
    End If
    
    'Clear the data.
    sht.Range("C5:D24").ClearContents
    
End Sub

Note that the two macros were adjusted to work with the specific workbook that contains a worksheet named "Printers". You can find this workbook in the Downloads section that follows.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Saturday, 30 June 2018

Last updated: 03/07/2018, 1 min read (without the code)

Get Laptop Battery Information Through VBA


Introduction



As the title indicates, in this post we will learn how to get several properties related to the battery of the laptop that runs our VBA code. Unfortunately, VBA does not have any built-in functionality that can be used to retrieve this kind of information. Therefore, with the help of WMI we will get the following battery properties:
  • Availability
  • Battery status
  • Chemistry
  • Estimated charge remaining
  • Estimated run time
  • Time on battery
  • Time to full charge

These are probably the most useful ones. If you need other attributes, you can check this link to find the additional properties that are available in the Win32_Battery class. The technique to retrieve any of the other property is similar to the one used below.



VBA code



The code was written in a way that all the functions are based on the GetBatteryObject function, which returns an object containing several properties about the laptop's battery. The other functions simply use this object to check if a particular property exists. If yes, then they retrieve the value of that property. If the returned numeric value is an enumeration, as it is in the case of GetBatteryAvailability, GetBatteryStatus and GetBatteryChemistry functions, the code returns the actual string value. In the other cases, the functions return the numeric value that corresponds either to a charge percentage or to time (minutes/seconds).

Option Explicit

'-------------------------------------------------------------------------------------------
'This module contains some functions for retrieving information about the laptop's battery.
'The main function is the GetBatteryObject, which retrieves an object containing several
'properties about the laptop's battery. The other functions simply use this object to
'retrieve the appropriate property. The GetBatteryObject function uses WMI to query
'the Win32_Battery class of Windows. More information about this class can be found in:
'https://docs.microsoft.com/en-us/windows/desktop/cimwin32prov/win32-battery

'Written By:    Christos Samaras
'Date:          30/06/2018
'Last Updated:  03/07/2018
'E-mail:        xristos.samaras@gmail.com
'Site:          https://www.myengineeringworld.net
'-------------------------------------------------------------------------------------------

Private Function GetBatteryObject() As Object
    
    '----------------------------------------------------------------------------
    'Returns an object containing several properties about the laptop's battery.
    'The function is private, so as to not be visible from Excel worksheets.
    '----------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim computer    As String
    Dim wmiService  As Object
    Dim colItems    As Object
    Dim item        As Object
    
    On Error Resume Next
    
    'Set the computer.
    computer = "."
    
    'The root\cimv2 namespace is used to access the Win32_Battery class.
    Set wmiService = GetObject("winmgmts:\\" & computer & "\root\cimv2")
    
    'A select query is used to get a collection of battery objects.
    Set colItems = wmiService.ExecQuery("SELECT * FROM Win32_Battery", , 48)
    
    'Note, an alternative here will be to select only the properties you need and not all (the asterisk means all).
    'In that case, the query could be written like this:
    'Set colItems = wmiService.ExecQuery("SELECT Availability, BatteryStatus, Chemistry," + _
                                                "EstimatedChargeRemaining, EstimatedRunTime," + _
                                                "TimeOnBattery, TimeToFullCharge FROM Win32_Battery", , 48)
        
    'Get the first object that is not null.
    For Each item In colItems
        If Not IsNull(item) Then Set GetBatteryObject = item
        Exit Function
    Next
    
    'If no battery object is found, return nothing.
    Set GetBatteryObject = Nothing

    On Error GoTo 0
    
End Function

Public Function GetBatteryAvailability() As String
    
    '-----------------------------------------
    'Returns the availability of the battery.
    '-----------------------------------------
    
    'Declaring the necessary variables.
    Dim batteryObject   As Object
    Dim i               As Integer
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetBatteryAvailability = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the Availability property can be retrieved.
    If IsNull(batteryObject.Availability) Then
        GetBatteryAvailability = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    i = CInt(batteryObject.Availability)
    If i < 1 Or i > 21 Then
        GetBatteryAvailability = "Battery Availability Error"
        Exit Function
    End If
    
    'Use the numeric value to return the actual string value.
    GetBatteryAvailability = Array("Other", "Unknown", "Running/Full Power", "Warning", "In Test", "Not Applicable", _
                                   "Power Off", "Off Line", "Off Duty", "Degraded", "Not Installed", "Install Error", _
                                   "Power Save - Unknown", "Power Save - Low Power Mode", "Power Save - Standby", _
                                   "Power Cycle", "Power Save - Warning", "Paused", "Not Ready", "Not Configured", _
                                   "Quiesced")(i - 1)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

Public Function GetBatteryStatus() As String
    
    '-----------------------------------
    'Returns the status of the battery.
    '-----------------------------------
    
    'Declaring the necessary variables.
    Dim batteryObject   As Object
    Dim i               As Integer
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetBatteryStatus = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the BatteryStatus property can be retrieved.
    If IsNull(batteryObject.BatteryStatus) Then
        GetBatteryStatus = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    i = CInt(batteryObject.BatteryStatus)
    If i < 1 Or i > 11 Then
        GetBatteryStatus = "Battery Status Error"
        Exit Function
    End If
    
    'Use the numeric value to return the actual string value.
    GetBatteryStatus = Array("Discharging", "On A/C", "Fully Charged", "Low", "Critical", "Charging", "Charging High", _
                             "Charging Low", "Charging Critical", "Undefined", "Partially Charged")(i - 1)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0

End Function

Public Function GetBatteryChemistry() As String
    
    '---------------------------------
    'Returns the battery's chemistry.
    '---------------------------------
    
    'Declaring the necessary variables.
    Dim batteryObject   As Object
    Dim i               As Integer
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetBatteryChemistry = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the Chemistry property can be retrieved.
    If IsNull(batteryObject.Chemistry) Then
        GetBatteryChemistry = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    i = CInt(batteryObject.Chemistry)
    If i < 1 Or i > 11 Then
        GetBatteryChemistry = "Battery Chemistry Error"
        Exit Function
    End If
    
    'Use the numeric value to return the actual string value.
    GetBatteryChemistry = Array("Other", "Unknown", "Lead Acid", "Nickel Cadmium", "Nickel Metal Hydride", _
                                "Lithium-ion", "Zinc air", "Lithium Polymer")(i - 1)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

Public Function GetEstimatedChargeRemaining() As Integer
    
    '-------------------------------------------------------------
    'Returns the remaining charge of the battery as a percentage.
    'A value of 100 means that the battery is fully charged.
    '-------------------------------------------------------------
    
    'Declaring the necessary variable.
    Dim batteryObject   As Object
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetEstimatedChargeRemaining = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the EstimatedChargeRemaining property can be retrieved.
    If IsNull(batteryObject.EstimatedChargeRemaining) Then
        GetEstimatedChargeRemaining = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    GetEstimatedChargeRemaining = CInt(batteryObject.EstimatedChargeRemaining)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

Public Function GetEstimatedRunTime() As Long
    
    '------------------------------------------------------------------------------------------------------------
    'Returns the time (in minutes) to battery charge depletion under the present load conditions if the utility
    'power is off, or lost and remains off, or the laptop is disconnected from a power source.
    'A value of 30 means that the battery can continue providing power to your laptop for about 30 minutes.
    '------------------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variable.
    Dim batteryObject   As Object
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetEstimatedRunTime = "Battery Object Error"
        Exit Function
    End If
        
    'Check if the EstimatedRunTime property can be retrieved.
    If IsNull(batteryObject.EstimatedRunTime) Then
        GetEstimatedRunTime = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    GetEstimatedRunTime = CLng(batteryObject.EstimatedRunTime)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

Public Function GetTimeOnBattery() As Long
        
    '------------------------------------------------------------------------------------------------------------------
    'Returns the elapsed time (in seconds) since the computer system's UPS last switched to battery power or the time
    'since the system or UPS was last restarted, whichever is less. If the battery is "online", 0 (zero) is returned.
    '------------------------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variable.
    Dim batteryObject   As Object
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetTimeOnBattery = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the TimeOnBattery property can be retrieved.
    If IsNull(batteryObject.TimeOnBattery) Then
        GetTimeOnBattery = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    GetTimeOnBattery = CLng(batteryObject.TimeOnBattery)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

Public Function GetTimeToFullCharge() As Long
    
    '-------------------------------------------------------------------------------------------------------------
    'Returns the remaining time (in minutes) to charge the battery fully at the current charging rate and usage.
    'A value of 45 means that the battery will be fully charged in about 45 minutes.
    '-------------------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variable.
    Dim batteryObject   As Object
            
    On Error Resume Next
    
    'Get the battery object.
    Set batteryObject = GetBatteryObject()
    If Not IsObject(batteryObject) Then
        GetTimeToFullCharge = "Battery Object Error"
        Exit Function
    End If
    
    'Check if the TimeToFullCharge property can be retrieved.
    If IsNull(batteryObject.TimeToFullCharge) Then
        GetTimeToFullCharge = "Battery Property Error"
        Exit Function
    End If
    
    'Get the numeric value from the property.
    GetTimeToFullCharge = CLng(batteryObject.TimeToFullCharge)
    
    'Release the battery object.
    Set batteryObject = Nothing
    
    On Error GoTo 0
    
End Function

The above code is contained in the sample workbook that you will find in the Downloads section that follows. As a bonus tip, you will also find there a "battery chart" that shows the remaining charge of your laptop's battery.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.