google apps script: built-in spreadsheet functions fail in script - wrong syntax?

Question

I am learning GAS and want to use spreadsheet functions within my script. As a test I did a simple case but on save it threw "reference Error: 'Left' is not defined." I've looked through examples of code and can't see an alternate syntax.

function testLeft(){
  return LEFT("abcdef",3); 
}

A second simple test, same result

function testNow(){
return Now()
}

Any suggestions? My wild guess is that there is a special syntax within scripts for using a built-in spreadsheet function. Or maybe not all functions available directly in spreadsheets are available for use in GAS?

Thanks.

Answer 1

Unfortunately spreadsheet functions are not available in Google Apps Script. In this case you can use JavaScript's substring() method to get the portion of the string you desire.

Answer 2

This is my attempt at simulating the mid Function

function fMid(varInStr, intPlace) {
//GAS does not have a Mid Function so I have made this one for now
//varInStr is the input string and you want a character returned from it at a given position
//intPlace is the position of the character you want
//Example
//=fMid("123456789", 9) returns "9"

var P
var N

P = intPlace -1
N = intPlace

return varInStr.substring(P,N)
};

Answer 3

I am from a VBA background and I have found this site

http://excelramblings.blogspot.co.uk/2012/05/google-apps-script-equivalents-for.html

provided by Bruce Mcpherson very helpful lots of ready made functions for copying and pasting into your Google App Script especially if you are converting from an Excel spreadsheet to a Google spreadsheet.

Bruces Code for LEFT:

function Left(str,optLen) {
  return Mid( str, 1 , optLen);
}

And to use the above LEFT function you will also need Bruces Mid function:

function Mid (str,optStart,optLen) {
  var start = IsMissing (optStart) ? 0 : optStart - 1;
  var length = IsMissing (optLen) ?  Len(str) - start + 1 : optLen ;
  DebugAssert( str.slice, str + ' is not a valid string for Mid function');
  return  str.slice ( start, start + length);