Contents
alternate_email

  ondemand_video   animation
Reviewed: 2021-03-28

Popup Help

Custom Functions

AddRowToProjectList(name, circuitranges, assetranges, productssheet, flags)

adds project row to Projects sheet adds Projects sheet if it doesn't exist used by Addon to automate project mgmt
Parameters:
Name Example Data Description
name "My Project" project name
circuitranges "" comma delimited list of circuit ranges
assetranges "" comma delimited list of asset ranges
productssheet "" comma delimited list of product ranges
flags "remove, ignore" use ignore to ignore project for deploy all or remove to delete from server
Source:

color(strRGB)

Returns js object {r,g,b} an internal function for other script functions
Parameters:
Name Example Data Description
strRGB "1,1,1" string based 1,1,1 webGL color vector
Source:

colorColumn(displayString)

Returns the displayString as a result for the cell processes all cells below for 1,1,1 webGL based colors including color:, ecolor: and decolor: prefixed values and sets the cell background using onEdit operator because the onEdit is used, the color update takes a few seconds values in adjacent rows with values in the same column are processed once an empty row/cell is encountered, the cell coloring stops.
Parameters:
Name Example Data Description
displayString "Header Description" usually a column header, value to be displayed in cell
Source:

colorHeader(displayString, cellToWatch, firstCellToSet, secondCellToSet)

Returns displayString as a result for the cell process the 2nd parameter as a cell range to process for 1,1,1 webGL based colors including color:, ecolor: and decolor: prefixed values and sets cells based on the 3rd, 4th, ... parameters as range to set the background color because the onEdit is used, the color update takes a few seconds values in adjacent rows with values in the same column are processed once an empty row/cell is encountered, the cell coloring stops.
Parameters:
Name Example Data Description
displayString "Header Description" usually a column header, value to be displayed in cell
cellToWatch D1 cell that might contain a webGL (1,1,1) string based color value
firstCellToSet D1 cell to set background color
secondCellToSet C1 second cell to set background color - as many as you like ...
Source:

colorRGB255(webGLColor)

Returns html rgb(255,255,255) color for webGL 1,1,1 color
Parameters:
Name Example Data Description
webGLColor "1,0,1" r,g,b vector, 0-1 color values
Source:

createSheetFromTemplate(sheetName, template)

Create sheet from json template script function, array is native javascript used by addon to create templates
Parameters:
Name Example Data Description
sheetName sheet name
template json array of cell descriptions
Source:

forceEval(sheetName, row, col)

forces recalc of a cell including clearing Sheets cache via SpreadsheeetApp.flush() after clearing cell formula
Parameters:
Name Example Data Description
sheetName "My Sheet" string value
row 1 row index - starts with 1
col 1 column index - starts with 1 = A
Source:

getCellRangeFromRangeString(sheetRange)

Extracts cell range as a string from a sheet range string i.e. "Sheet!A1:B2" returns "A1:B2" sheet name required in range
Parameters:
Name Example Data Description
sheetRange "Sheet!A1:B2"
Source:

getCSVFirstCellsFromSheet(sheetName)

gets top left cell for all possible Data Tables returns cell ranges as comma delimited list ranges include sheet names getDataRangesForSheet calls this function
Parameters:
Name Example Data Description
sheetName "My Sheet" string value
Source:

getCSVRangeForCell(cellStringList)

returns a string representing a range containing the data table from from the range string containing a cell passed uses Range.getDataRegion() internally to determine table boundaries similar to getTablesForCells uses getActiveRange similar to getStringForRange
Parameters:
Name Example Data Description
cellStringList "Sheet1!A1,Sheet1!A10,Sheet2!A1" list of top left cells of Data Tables
Source:

getDataRangesForSheet(sheetName)

Returns comma delimited list of ranges for Data Tables start in the A column, with headers in the first column table end is first row with column A empty rows with an empty column A are ignored (i.e. comments) this function is essential for the Projects Sheet = getTablesForCells(getCSVFirstCellsFromSheet(sheet))
Parameters:
Name Example Data Description
sheetName "Sheet Name" Sheet Name to process range for Data Tables.
Source:

getJSONForSheet(sheetName, rowStart, rowLimit, block)

Returns json data for a sheet 50k return limit (sheets cell based) rows are returned in an array
Parameters:
Name Example Data Default Description
sheetName "Sheet Name" Sheet Name to process to json.
rowStart 1 1 first row to include in output.
rowLimit 100 100 row limits - i.e. 1 to 100 = 100 rows
block 1 1 block index - if over 50k, use 2 for the 2nd block, etc
Source:

getJSONFromCSVSheet(sheetName, refreshFormulas, formulas)

returns a sheet contain a Data Table as a json array of rows column headers are expected as first row, data is CSV Style this is an important function for addon is used for Configuration and Projects sheets
Parameters:
Name Example Data Default Description
sheetName "My CSV Sheet" sheet to process
refreshFormulas false false forceEval cells to update data and break caching
formulas false false export formulas instead of values
Source:

getSheetFromRangeString(rangeString)

Extracts sheet name as a string from a sheet range string i.e. 'Sheet name'!A1:B2 returns "Sheet name" sheet name (Sheet) required in range
Parameters:
Name Example Data Description
rangeString "Sheet name!A1:B2" string based range including sheet name
Source:

getStringForRange(sheetRange)

returns string representing passed native range uses getActiveRange() - so cut and paste requires recalcs also caching can be an issue, focus cell and refresh formula to update
Parameters:
Name Example Data Description
sheetRange "Sheet!A1" native sheet range
Source:

getTablesForCells(cellStringList)

returns a comma delimited list of ranges containing data tables from a list of of cells representing by strings including sheet name. is designed to consume output of getCSVFirstCellsFromSheet either script or cell based called by getDataRangesForSheet uses Range.getDataRegion() internally to determine table boundaries
Parameters:
Name Example Data Description
cellStringList "Sheet1!A1,Sheet1!A10,Sheet2!A1" list of top left cells of Data Tables
Source:

gridJoin(delimiter, rangeValues)

Similar to join(delimiter) in Javascript takes a range and returns a delimited list of values as a string
Parameters:
Name Example Data Description
delimiter "," delimiter to separate cell data i.e. comma, tab or space
rangeValues B2:E10 range of cells used for input values
Source:

JSONArrayMax(jsonArray)

array of arrays to be broken into list of cells containing array each string form of json inputs and outputs used primarily for audio data
Parameters:
Name Example Data Description
jsonArray "[1,5,12.1,0,1]" array of values (sing form of json)
Source:

JSONArraysToCells(jsonArrays)

array of arrays to be broken into list of cells containing array each string form of json inputs and outputs used primarily for audio data
Parameters:
Name Example Data Description
jsonArrays "[[1][2]]" array of arrays
Source:

JSONArrayToPipeData(strJSONArray, maxValue)

takes a json array in string form and returns a pipe delimited result ('|') used primarily for audio data
Parameters:
Name Example Data Description
strJSONArray "[1,5,12.1,0,1]" array of values to convert = 1|5|12.1 ...
maxValue 10 max to clip array
Source:

mergeCSVRanges()

legacy - takes range(s) representing Data Tables as argument(s) and merges them into one result takes in variable number of arguments, each a range containing a data table/csv data
Source:

mergeCSVRangeStrings(rows, jsonResult)

legacy - takes string based range(s) representing Data Tables as argument(s) and merges them into one result supports |||| operator for data based in external workbooks embedded or script based function - input rows can be a range based list or a native json array results can be json or cell based output
Parameters:
Name Example Data Description
rows array of strings, each respresenting a datarange
jsonResult true for script native data result
Source:

refreshSheetQueries(sheetName)

forces recalc of all cells in a sheet including clearing Sheets cache via SpreadsheeetApp.flush() after clearing cell formulas
Parameters:
Name Example Data Description
sheetName "My Sheet" string value
Source:

SetCSVSheetValue(sheetName, rowIndex, field, value)

for use CSV sheets (one data table per sheet) Projects and Configuration sheets make use of this
Parameters:
Name Example Data Description
sheetName "my sheet" sheet name string
rowIndex 1 1 is first row
field "column" column header
value 1 field value
Source:

truncateEmptyCSVColumns(rows)

analyzes an array of json rows and removes columns if the column is empty in all rows script/json based parameters
Parameters:
Name Example Data Description
rows array of json maps
Source: