Google Script to Split Right String Values

Question

I would like to perform Split function for a specific range. Example, If i have a string like "HYT008", I need to have only 008 as result, means I would like to split last 3 letters from a string.

Here is the scenario -

In Google Sheet I will perform this condition as =RIGHT(A2:A,3)

In Google Apps Script, I need to perform the action.

what is the perceived benefit of having a script do this over having a formula do it? — Erik Tyler
– Erik Tyler, Commented Dec 18, 2020 at 17:34
Here's the JavaScript string methods: developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… — Cooper
– Cooper, Commented Dec 18, 2020 at 17:35
My Sheet is getting stuck while i perform the Right formula, — Moses
– Moses, Commented Dec 18, 2020 at 17:35

Marios · Accepted Answer · 2020-12-18 18:09:55Z

2

Explanation:

There are multiple ways you can do that in JavaScript.

One way is to use the slice method and get the last 3 elements of your string: string.slice(-3).
then you can use map to apply this operation to the full column of your choice:

sh.getRange('A2:A'+sh.getLastRow()).getValues().flat().map(v=>[v.slice(-3)]);

Google Apps Script Solution:

The following script will get the values in column A, get the last 3 elements and paste them in column B:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // adjust this to the name of your sheet
  const values = sh.getRange('A2:A'+sh.getLastRow()).getValues().flat().map(v=>[v.slice(-3)]);
  sh.getRange(2,2,values.length,1).setValues(values); // paste them in column B
}

Sheet used for the code snippet:

Google Sheets Formula Solution:

Here you don't need to define the last element of your column.

=arrayformula(if(len(A2:A),right(A2:A,3),""))

Result:

edited Dec 18, 2020 at 18:09

answered Dec 18, 2020 at 17:40

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marios Over a year ago

@Moses I added also an arrayformula in case you can use it, could be quite useful.

NightEye Over a year ago

Complete answer with good explanation. +1

Collectives™ on Stack Overflow

Google Script to Split Right String Values

1 Answer 1

Explanation:

Google Apps Script Solution:

Google Sheets Formula Solution:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation:

Google Apps Script Solution:

Google Sheets Formula Solution:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related