dateToYMD

Returns a date as a string in YYYY-MM-DD format.

Given a date from a spreadsheet cell, returns the date in international date format (aka unix format). Used for APIs that require dates in unix format.

getDomainName

Extracts the domain from one or many URLs

getMonday

Returns the first day of the week. Useful for formatting headers.

hashToParam

Converts a hash to a query parameter

isArray

Returns true if obj is an array.

isColumn

Returns true if the array is actually in columns

isInFuture

Determines if some date is in the future.

jsonifyCol

Convert an array into a JSON string

jsonStringify

Converts an object to a JSON string

percentDiff

Returns the percentage difference between two numbers.

rangeToUrlString

Returns a comma separated string from a range of cells. Each one will be encoded so it can safely be used in a URL.

ratioUp

Returns a ratio of two numbers and rounds it up to decimal places as a percentage.

stripUrlScheme

Removes HTTP or HTTPS from the beginning of one or more URLs

strToArray

Converts one string into an array if it's a string

Function

=dateToYMD(d)

Parameters

Name Type Description
d date Date from the spreadsheet

Returns

Column Description
String of the date in YYYY-MM-DD format

Converts date to YYYY-MM-DD
Converts a date to unix time format (YYYY-MM-DD)

Cells:
  A1: 11/01/2011
=dateToYMD(A1)
// Returns 2011-11-01

Function

=getDomainName(urlRange)

Parameters

Name Type Description
urlRange string URL

Returns

Column Description
Domain name as a string.

Get the domain name
Extracts the domain name from any URL

// Returns "domain.com"
=getDomainName("http://www.domain.com/blog")

Get the domain name from a range
Extracts the domain names from a range of cells

=getDomainName(A1:A10)

Function

=getMonday(d)

Parameters

Name Type Description
d date Any date you want to find the start of the week for

Returns

Column Description
Date that is Monday (the start of the week)

Returns the beginning of the week
Returns the beginning of the week

Cells:
   A1: 11/18/2011
=getMonday(A1)
// Nov 18th is a Friday.  This will return 11/14/2011 which is Monday.

Function

=hashToParam(hashes)

Parameters

Name Type Description
hashes hash Hash

Negative change gives you a negative number
Returns the percentage difference between two numbers

var queryString = hashToParam({
   "key"     : "1234abcd5678",
   "param"   : "value",
   "param2"  : "value2"
}); // => "key=1234abcd5678¶m=value¶m2=value2"

Function

=isArray(obj)

Parameters

Name Type Description
obj obj Some object

Returns

Column Description
Returns true if the object is an array

 

isArray([1, 2, 3]); // => true
isArray("hello world"); // => false

Function

=isColumn()

Parameters

 

// myArray is A1:A3
isColumn(myArray); // => true
// myArray is A1:C1
isColumn(myArray); // => false
// myArray is A1
isColumn(myArray); // => false

Function

=isInFuture(date)

Parameters

Name Type Description
date date The starting date

Returns

Column Description
True if the date is in the future, false if it's not.

Check if date is in the future
Returns true if the date is > today

  A1: 11/01/2020
=isInFuture(A1) // true

Function

=jsonifyCol(inArray)

Parameters

Name Type Description
inArray array Range of cells

Returns

Column Description
JSON string of the first column in an array

Array to JSON
Returns a JSON string of the array that was passed in.

var myArray = [[1, 2], [3, 4]];
jsonifyCol(myArray); // => '[1, 3]'
jsonifyCol("string"); // => '["string"]';

Function

=jsonStringify(obj)

Parameters

Name Type Description
obj object Object you want to make in to a JSON string

Returns

Column Description
JSON string

Function

=parseUrl(URL)

Details

Separates a URL into seperate pieces (such as the domain name, the path, or the query string)

Columns returned

  • url: http://www.ora.com:80/goodparts?q#fragment
  • scheme: http
  • slash: //
  • host: www.ora.com
  • port: 80
  • path: goodparts
  • query: q
  • hash: fragment
Credit: JavaScript: The Good Parts

Parameters

Name Type Description
URL string Url to parse

 

=parseUrl("http://www.ora.com:80/goodparts?q#fragment");

Function

=percentDiff(oldNumber, newNumber)

Parameters

Name Type Description
oldNumber number Old number
newNumber number New number

Returns

Column Description
Percentage difference from the old number to the new one.

Positive change gives you a positive number
Returns the percentage difference between two numbers

//
// A1: Yesterday's Visits
// A2: 100
// B1: Today's Visits
// B2: 200
// C1: Percentage difference:
// C2:
   =percentDiff(A2, B2) // 100%

Negative change gives you a negative number
Returns the percentage difference between two numbers

// A1: Yesterday's Visits
// A2: 100
// B1: Today's Visits
// B2: 75
// C1: Percentage difference:
// C2:
   =percentDiff(A2, B2) // -25%

Function

=rangeToUrlString(range)

Parameters

Name Type Description
range string A range of cells

Returns

Column Description
Query string

Convert to URL string
Turns an array or range into a comma delimited query string

Cells:
   A1: domain.com
   A2: domain.com/blog
=rangeToUrlString(A1:A2)
// => "domain.com,domain.com%2Fblog"

Function

=ratioUp(numerator, denominator, decimalPlaces)

Parameters

Name Type Description
numerator number Numerator
denominator number Denominator
decimalPlaces integer Number of decimal places

Returns the ratio of two numbers as a percentage
Returns the beginning of the week

=ratioUp(8, 43, 2)
// Returns 0.19

Function

=stripUrlScheme(url)

Parameters

Name Type Description
url string URL

Returns

Column Description
URL string without the protocol

From a range of URLs
Removes HTTP/HTTP from a range of URLs

A1: http://www.domain.com
A2: https://www.anotherdomain.com
B1: =stripUrlScheme(A1:A2)

Function

=strToArray(obj)

Parameters

Name Type Description
obj obj Object to convert to array

Returns

Column Description
Array

From a string
Returns a single dimentional array if given a string

strToArray("hello world"); // => ["hello world"]

From a array
Does nothing if it's already an array

strToArray(["an array"]); // => ["an array"]