Use Google Sheets RIGHT formula in scripts

Question

I have a spreadsheet with a bunch of numbers in one column. How can I use google sheets RIGHT formula to take the last 4 numbers and paste them into the adjacent column? I am trying to write a function in Google Apps Script for this. There isn't much documentation on this so I am lost.

Why are you lost? did you try this for cell A1 for example: right(A1,4) ? — soMario
Nothing, I don't even know where to begin. There isn't any documentation on how to apply the LEFT or RIGHT function to Google sheets. — Tarik Hodzic
I posted my answer. For future reference, keep in mind that google apps script uses javascript, not google sheet formulas. If you want to look for a way to get the last 4 digits of a value. Then you need to look for javascript solutions because only those are supported in google apps script. — soMario

soMario soMario · Accepted Answer · 2020-11-18T17:27:56

Explanation:

One way to implement the RIGHT google sheets formula in google scripts is to get the desired value as a string and then use slice(-4) to get the last 4 digits of that number. Of course there are many ways to achieve the same goal but this is just one of them.
To get the value of a cell as a string, you can use getDisplayValue(). The following script will get the display value of cell A1 and it will set the value of B1 to the last 4 digits of cell A1.

Code snippet:

Feel free to modify the sheet name (in my case Sheet1) and the input and output cells (in my case A1 is the input and B1 is the output).

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const value = sh.getRange('A1').getDisplayValue();
  const valueL4D = value.slice(-4);
  sh.getRange('B1').setValue(valueL4D);
}

Input (A1) / Output (B1):

Use Google Sheets RIGHT formula in scripts

1 Answers

Explanation:

Code snippet: