How to use Substring in Google Apps Script

This post will guide you on how to use Substring method in Google Apps Script.

Please refer to this post if you are running scripts for the first time.

Syntax

Google Apps Script uses the Javascript Language. This is the syntax for Substring method.

string.substring(start)
string.substring(start, end)

Parameters

start – refers to the index of the first character to be included in the returned substring

end (optional) – refers to the index of the first character to be excluded from the returned substring. If not specified, then it will return the rest of the string.

note: index starts from 0

Example of usage

function myFunction() {
  let dir = "rootfolder/image1.png";

  console.log("dir.substring(11) returns: "+ dir.substring(11));
  console.log("dir.substring(11,17)) returns: "+ dir.substring(11,17));
}

You can see that dir.substring(11) will return substring from start index until the end of string.

dir.substring(11,17)) will only return substring that is between start and end index.

Getting substring from a cell using Google App Script

Let’s put this into a real use case. In this example, we are going to get the directory string from Column A and then return the image name in Column B.

Here is the code to do that.

function getImage() {
  var spreadssheet = SpreadsheetApp.getActiveSpreadsheet();
  let rows = spreadssheet.getLastRow();

  for(i =1; i <= rows; i++)
  {
    let dir = spreadssheet.getRange("A"+i).getValue(); 
    if(dir.includes("/"))
    {
      let image = dir.substring(dir.indexOf("/")+1);
      spreadssheet.getRange("B"+i).setValue(image); 
    }
    
  };
}

Let’s breakdown the above code.

First we need to get the current active spreadsheet. And also the number of rows that has values in it.

var spreadssheet = SpreadsheetApp.getActiveSpreadsheet();
let rows = spreadssheet.getLastRow();

Next, this will loop the code for n number of times (depending on the rows)

for(i =1; i <= rows; i++)

We get the dir value from column A.

let dir = spreadssheet.getRange("A"+i).getValue(); 

Next, we check if dir contains “/” character. If it does, then we continue to get the substring value.

if(dir.includes("/"))

Here we set the image value by using the substring method described above. But instead of using a start index, we use dir.indexOf("/")+1.

dir.indexOf("/") will get the index of char “/”. We add a +1 because we want to return the substring starting after the “/” character.

    {
      let image = dir.substring(dir.indexOf("/")+1);
      spreadssheet.getRange("B"+i).setValue(image); 
    }
    

So by running the code on Apps Script, you should see the following output on your active google spreadsheet.

What happens if the directory has one or more than one “/” character?

We just need to change the code for this:

let image = dir.substring(dir.indexOf("/")+1);

to use the lastIndexOf("/"). This will find the last “/” character and return the index of that.

function getImage() {
  var spreadssheet = SpreadsheetApp.getActiveSpreadsheet();
  let rows = spreadssheet.getLastRow();

  for(i =1; i <= rows; i++)
  {
    let dir = spreadssheet.getRange("A"+i).getValue(); 
    if(dir.includes("/"))
    {
      let image = dir.substring(dir.lastIndexOf("/")+1);
      spreadssheet.getRange("B"+i).setValue(image); 
    }
    
  };
}

Running the above code will return the following output.