If you work with Excel or with tabular data chances are you have to do quite a bit of data manipulation. I use Google Sheets instead of Excel for many reasons I won’t get into but one advantage is the JavaScript compatible Apps Script that allows you to develop custom functions similar to what you do in VB Script (Visual Basic for Excel). The power of Apps Script lies in leveraging the awesome power of JavaScript!

One thing I have to do a LOT is to translate lists. For example, lets say I have a “Category” column from a vendor in which the Categories are text strings but my eCommerce cart needs to be mapped to category IDs.

Very often I use the built in Excel-like function called SWITCH which allows you to search for something like a string and return another associated value. Such is the case when a eCommerce system requires Category ID instead of the text string.

A SWITCH statement has the form:

=SWITCH(A1, 

"Text Category", "10",

"Text Category 2", "11",

"Text Category 3", "12"

)

If the text on the left is found it is replaced with the value to the right.

This is very useful in many other use cases and a combination of concatenations is not unusual to create new data columns.

Using a SWITCH does have its limitations. One of which is the fact that I need to know that the statement is the exact same in every cell. If I want to centralize my translation lists I need another solution.

We can get around this by writing our own custom switch function in Apps Script! As a simple example we will actually create our own SWITCH function, very similar to how the App is preforming the function already but with better visibility and easier editing of the translation list.

JavaScript allows us much more versatility in manipulating lists, arrays and objects. Leveraging JavaScript you could build versatile functions to handle some of the most time consuming tasks in your workflow.

Creating a custom function is easy!

First we just need to open up a new Google Sheet then go to Tools > Script Editor . A new window opens with am empty function in it and an un-named file. Go ahead and name the file and save. Then delete all the contents and paste the below code.

/**
 * A custom function that converts category strings into a mapped category_id 
 *
 * @param {String} category string
 * @return {Number} id number of the category
 */

function createIds (categoryId) {  
  switch(categoryId){
    case 'Wall Sconce': categoryId = 3; break;
    case 'Pendant':     categoryId = 5; break;
    case 'Chandelier':  categoryId = 1; breakdefault: categoryId = 0;
  };
  return  categoryId; 
}

Click save and then click the “Play” or run button to run the script. Now back in your GSheet file go into a cell and enter =CREATEIDS(“Wall Sconce”)

You now have a custom function that takes the cell input and compares it against the cases and then returns the appropriate ID if found, if not then the default is returned.

Great! Now I have a custom function which I can use in building up my import sheet for my eCommerce platform and I only need to look at the function to check or update assignments.

Please note that in these blocks of code I am keeping things VERY VERY simple in the hopes you will take the initiative to create more complex use cases. There are additional validation and triggers that can be added to create more robust and useful functions.

Now let’s take this one step further and instead of creating our lookup list in a SWITCH statement let’s create another tab we can reference. If you are familiar with Excel there are other functions that do similar things however this is the basis for building more complex functions so master this first!

The code is as simple as:

/*
 * A custom function that converts category strings into a mapped category_id through a settings tab
 *
 * @param {String} category string from the vendor
 * @return {Number} id number of the category
 *
 * This a very basic example of using Apps Script to compare the value of a cell to a key in a list and return an associated value
 * There are no inputs, menus, or validation for simplity sake
 * This shows you how to quickly mock up functionality while using other validation scripts and UI interactions can be applied after using the same logic
 *
 */

//Create a a new custom function that takes the value of the cell as the paramter
function createIdsFromSettings (cat) {
  

  //Get the sheet instance of the config sheet as active so we can retrive values
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //Get the sheet incstance with the configurations
  var configSheet = ss.getSheetByName("Config"); //#### You can set this to the name of your sheet that has the comparison adta configuration
  
  //Get an object with the properties of the cells A1:A100 to B1:B100 in Config sheet
  var configRangeAB = configSheet.getRange('A1:B100'); //##### You can change this to any range of 2 adjacent columns
  
  //Get an object with an array of the values of the config range
  //This creates an object that contains arrays of values of the range 
  var configRangeABValues = configRangeAB.getValues();
  
  //Create an empty variable to use in the below for-in object loop
  var prop;
  
  //We are just checking to make sure the input is a String we can use regex to preform better validation or many other validation methods.
  if(typeof(cat) == 'string'){
    
  for(prop in configRangeABValues){
    
    //Here we check each value object for a value in the property array that matches the input (cat in our case)
    if(cat == configRangeABValues[prop][0]){
      //If we get a match we are pulling the next value out of that object's property array which is at index 1 
       return configRangeABValues[prop][1]; 
      
    } 
    
  }
    
  } else {
    
    //If the input is not a string (a number for instance) we just put the text below.
    //Alternatively we could color the cell or have some error message 
    return 'You need to enter a String!!';
    
  };

  SpreadsheetApp.flush();
  
}

Now we have a function that compares the value of the cell to that of column A in the Config sheets and returns the column B value.

Above is a very simple example that we will walk through.

  1. First in Apps Script API we have an instance of the active sheet that the code will need we do this first by creating a SpreadsheetApp instance.

2. Once we have the instance other methods become available on the sheet object. We then call the .getRange(‘Config’) method passing in the name of our look up list sheet, in this case I called it Config.

3. Once we get the range of our list (columns A & B) we need to get the values by calling getValues() on the resulting range object above.

4. Now that we have the values we just need to loop though the values of Column A until we get a match for the input of the cell. When we get a match we are going to return the other value from the Values array using bracket notation (ie. configRangeABValues[prop][1] ).

5. At the end of the app we just flush to make sure updates are applied in proper time.

Using custom functions in Google Sheets is incredibly powerful if you are looking to analyze or manipulate small to medium sized tabular data. You can extend the functionality of GSheets to preform much like a custom app with Apps Script but there are still some limitations.

One limitation is that GSheets currently lacks the ability to process more than 2 million cells. Which may sound like a lot but when working with eCommerce SKUs it can be just a single manufacturers data file!

I regularly work with large sets of data that are not suitable for desktop Excel or Google Sheets. In these cases I rely on SQL and MongoDB databases to preform many of the functions achievable in Google Sheets but with greater efficiency and more functionality.

In some of my other recent articles I discuss using MongoDB instead of Excel. Additionally, Apps Script offers powerful functionality for enhancing Google Sheets similar to what you can achieve with VB Script in Excel but in a language the web is written in!

Although this illustrates a simple use case, extending the functionality with triggers and validation rules will create a robust and useful function you can use when preparing your eCommerce import document. Checkout the Apps Script documentation for more information on the available classes and methods for Sheets objects.

If you have questions about using Apps Script or eCommerce catalog importing feel free to reach out to me!