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 |
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 |
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 |
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 ... |
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 |
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 |
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 |
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" |
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 |
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 |
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. |
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 |
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 |
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 |
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 |
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 |
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 |
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) |
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 |
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 |
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
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 |
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 |
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 |
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 |
Tools