JSpreadsheet - auto-mate/CheatSheetWiki GitHub Wiki
- Add On Change Function
- Basic Use
- Clear Completely
- Detailed Example (includes excel type functions list)
- Sending Table as JSON String
see Community Edition Quick Ref
simplest use ( after scripts loaded! )
x = jexcel(document.getElementById( '<DIV_ID_USED_FOR_SPREADSHEET>'),{ minDimensions:[10,20]})
x = jexcel(
document.getElementById( '<DIV_ID_USED_FOR_SPREADSHEET>'),
{ minDimensions:[10,20],
onchange: function(instance) {
alert(jexcel.current.highlighted[0].cellIndex+":"+jexcel.current.highlighted[0].parentElement.rowIndex);
}
})
jexcel(document.getElementById( '<DIV ID USED FOR SPREADSHEET>')).destroy()
Require Material Icons link for Toolbar or Self Host.
Includes examples of
- Multiple sheets
- Referencing the Sheet By different csvFileNames
- List of Functions added as extra toolbar
- Readonly areas
- Dropdowns
- Frozen Columns
- Conditional Formating
- Additional Styles
Code (NB add ",stripHTML: false" to column definition to use special formulas)
<html>
<head>
<!-- jsuites version 3.9.9 -->
<!-- jexcel version 4.5.0 -->
<script src="../frameworks/ce-master/ce-master/dist/jexcel.js"></script>
<script src="../frameworks/jsuites/jsuites-3.9.9/dist/jsuites.js"></script>
<link rel="stylesheet" href="../frameworks/ce-master/ce-master/dist/jexcel.css" type="text/css" />
<link rel="stylesheet" href="../frameworks/jsuites/jsuites-3.9.9/dist/jsuites.css" type="text/css" />
<link rel="stylesheet" type="text/css" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<style>
.ahFunc {
margin-left: 10%;
width:80%;
}
</style>
</head>
<body>
<!-- LOCATION FOR SPREADSHEET -->
<div id="spreadsheet"></div>
<script>
var changed=function(instance,e) {
console.log("changed");
console.log("changed Col: "+e.cellIndex);
console.log("changed Row: "+e.parentElement.rowIndex);
}
var selection=function(instance) {
console.log("Selected Col: " + instance.getElementsByClassName("highlight-selected")[0].cellIndex);
console.log("Selected Row: " + instance.getElementsByClassName("highlight-selected")[0].parentElement.rowIndex);
}
/* DATA FOR SHEET 1 */
var data1 = [
[ 'Crayons Crayola only (No Rose Art)', 2, '5.01', '=B1*C1' ],
[ 'Colored Pencils Crayola only', 2, '4.41', '=B2*C2' ],
[ 'Expo Dry-erase Markers Wide', 4, '3.00', '=B3*C3' ],
[ 'Index Cards Unlined', 3, '6.00', '=B4*C4' ],
[ 'Tissues', 10, '1.90', '=B5*C5' ],
[ 'Ziploc Sandwich-size Bags', 5, '1.00', '=B6*C6' ],
[ 'Thin Markers Crayola only', 2, '3.00', '=B7*C7' ],
[ 'Highlighter', 4, '1.20', '=B8*C8' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)' ],
];
/* COLUMN STYLE FOR SHEET 1 */
columns1 = [
{ type: 'text', title:'Product', width:'300',readOnly:true },
{ type: 'dropdown', title:'Qtd', width:'80',source:['1','2','3'] },
{ type: 'text', title:'Price', width:'100', mask:'#.##,00', decimal:',' },
{ type: 'text', title:'Total', width:'100' },]
/* DATA FOR SHEET 2 */
var data2 = [
[ 'AA', 20, '15.01', '=B1*C1' ],
[ 'BB', 20, '14.41', '=B2*C2' ],
[ 'CC', 40, '13.00', '=B3*C3' ],
[ 'DD', 30, '16.00', '=B4*C4' ],
[ 'EE', 1, '0.90', '=B5*C5',,,,,'=SUM(C1:D4)' ],
[ 'FF', 50, '11.00', '=B6*C6' ],
[ 'GG', 20, '13.00', '=B7*C7' ],
[ 'HH', 40, '11.20', '=B8*C8' ],
[ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '=SUM(D1:D8)' ],
];
/* COMBINED DATA FOR TOTAL WORKBOOK */
var sheets = [
{
sheetName: 'D1',
csvFileName:'D1',
//MIN COLS AND ROWS FOR THIS SHEET
minDimensions:[60,80],
//LOAD DATA
data:data1,
// COL STYLE FOR THIS SHEET
columns:columns1,
// FREEZE COUNT
freezeColumns:2,
tableOverflow: true,
tableWidth:'1700px',
tableHeight:'1000px',
onchange: changed,
onselection:selection,
// RUN ON UPDATED
updateTable:function(instance, cell, col, row, val, label, cellName) {
if (cell.innerHTML == 'Total') {
// SET ROW BACK COLOR
cell.parentNode.style.backgroundColor = '#fffaa3';
}
if (cell.parentElement.children[1].innerText=="Total") {
cell.classList.add('readonly');
// OVERIDE CLASS FOR BORDERS
cell.style.borderTop = "solid";
cell.style.borderBottom = "solid";
cell.style.borderLeft = "none";
cell.style.borderRight = "none";
cell.style.borderColor = "black";
}
if (col == 3) {
if (parseFloat(label) > 10) {
cell.style.color = 'red';
} else {
cell.style.color = 'green';
}
}
},
},
{
sheetName: 'D2',
csvFileName:'D2',
allowComments:true,
minDimensions:[40,60],
data:data2,
toolbar:[
{
type: 'i',
content: 'undo',
onclick: function() {
jspreadsheet.current.undo();
}
},
{
type: 'i',
content: 'redo',
onclick: function() {
jspreadsheet.current.redo();
}
},
{
type: 'i',
content: 'save',
onclick: function () {
jspreadsheet.current.download();
}
},
{
type: 'select',
k: 'font-family',
v: ['Arial','Verdana']
},
{
type: 'select',
k: 'font-size',
v: ['9px','10px','11px','12px','13px','14px','15px','16px','17px','18px','19px','20px']
},
{
type: 'i',
content: 'format_align_left',
k: 'text-align',
v: 'left'
},
{
type:'i',
content:'format_align_center',
k:'text-align',
v:'center'
},
{
type: 'i',
content: 'format_align_right',
k: 'text-align',
v: 'right'
},
{
type: 'i',
content: 'format_bold',
k: 'font-weight',
v: 'bold'
},
{
type: 'color',
content: 'format_color_text',
k: 'color'
},
{
type: 'color',
content: 'format_color_fill',
k: 'background-color'
},
// ADDED TO SAMPLE
{
type: 'i',
content: 'gesture',
onclick: function() {
for (i = 0; i < jspreadsheet.current.highlighted.length; i++ ) {
rowToRand = jspreadsheet.current.highlighted[i].attributes[1].value;
colToRand = jspreadsheet.current.highlighted[i].attributes[0].value;
jspreadsheet.current.setValueFromCoords(colToRand,rowToRand,"=RAND()");
}
}
},
{
type: 'i',
content: 'refresh',
onclick: function() {
jspreadsheet.current.refresh();
}
},
{
type: 'i',
content: 'functions',
onclick: function() {
var sheetName = "\""+jspreadsheet.current.getConfig().csvFileName+"\"";
nE = document.createElement('DIV');
nE.id="SF";
nE.style.position = 'absolute';
nE.style.zIndex = 100;
nE.style.top = jspreadsheet.current.highlighted[0].offsetTop+"px";
nE.style.left = jspreadsheet.current.highlighted[0].offsetLeft+jspreadsheet.current.highlighted[0].offsetWidth+"px";
nE.style.width = '10%';
nE.style.height = '150px';
nE.style.backgroundColor = '#f3f3f3';
nE.style.fontFamily = "Arial";
nE.style.borderRadius = '1%';
nE.style.boxShadow = '4px 3px 15px 2px';
var row=jspreadsheet.current.highlighted[0].attributes[1].value;
var col=jspreadsheet.current.highlighted[0].attributes[0].value;
nE.innerHTML="<h3 class='ahFunc' >Select Function</h3>"+
"<SELECT class='ahFunc'>"+functionsList+"</SELECT>"+
"<br><button class='ahFunc' onclick='AddFunction("+sheetName+","+row+","+col+")'>Add</button>" +
"<br><button class='ahFunc' onclick='CancelAddFunction()'>Cancel</button>";
document.getElementById('spreadsheet').appendChild(nE) ; //must be in container
}
},
],
}
];
/* CREATE SPREADSHEET */
jspreadsheet.tabs(document.getElementById('spreadsheet'), sheets);
/* DISPLAY SHEET DATA AS JSON DATA IN CONSOLE */
for ( i = 0; i < sheets.length; i++ ) {
console.log(JSON.stringify( sheets[i].data ))
}
function loadNewData() {
jspreadsheet.current.setData([{"0":"10.8"},{"0":"10.7"},{"0":"=SUM(A1:A2)"}]);
}
function AddFunction(sheetName,row,col) {
for (n = 1; n < jexcel.length; n++ ) {
if (document.getElementById('spreadsheet').jexcel[n].getConfig().csvFileName == sheetName) {
sheetRef=n;
}
}
document.getElementById('spreadsheet').jexcel[sheetRef].setValueFromCoords(col,row,"="+document.getElementById("SF").getElementsByTagName("select")[0].value);
document.getElementById("SF").remove();
}
function CancelAddFunction() {
//jspreadsheet.current.highlighted[i].attributes[1].value;
//jspreadsheet.current.highlighted[i].attributes[0].value;
document.getElementById("SF").remove();
}
functionsList =
"<option>DATE (year, month, day) </option>"+
"<option>DATEVALUE (date_) </option>"+
"<option>DAY (serial_number) </option>"+
"<option>DAYS (end_date, start_date) </option>"+
"<option>DAYS360 (start_date, end_date, method) </option>"+
"<option>EDATE (start_date, months) </option>"+
"<option>EOMONTH (start_date, months) </option>"+
"<option>HOUR (serial_number) </option>"+
"<option>INTERVAL (second) </option>"+
"<option>ISOWEEKNUM (date) </option>"+
"<option>MINUTE (serial_number) </option>"+
"<option>MONTH (serial_number) </option>"+
"<option>NETWORKDAYS (start_date, end_date, holidays) </option>"+
"<option>NETWORKDAYS.INTL (start_date, end_date, weekend, holidays) </option>"+
"<option>NOW () </option>"+
"<option>SECOND (serial_number) </option>"+
"<option>TIME (hour, minute, second) </option>"+
"<option>TIMEVALUE (time_text) </option>"+
"<option>TODAY () </option>"+
"<option>WEEKDAY (serial_number, return_type) </option>"+
"<option>WEEKNUM (serial_number, return_type) </option>"+
"<option>WORKDAY (start_date, days, holidays) </option>"+
"<option>WORKDAY.INTL (start_date, days, weekend, holidays) </option>"+
"<option>YEAR (serial_number) </option>"+
"<option>YEARFRAC (start_date, end_date, basis) </option>"+
"<option>FINDFIELD (database, title) </option>"+
"<option>DAVERAGE (database, field, criteria) </option>"+
"<option>DCOUNT (database, field, criteria) </option>"+
"<option>DCOUNTA (database, field, criteria) </option>"+
"<option>DGET (database, field, criteria) </option>"+
"<option>DMAX (database, field, criteria) </option>"+
"<option>DMIN (database, field, criteria) </option>"+
"<option>DPRODUCT (database, field, criteria) </option>"+
"<option>DSTDEV (database, field, criteria) </option>"+
"<option>DSTDEVP (database, field, criteria) </option>"+
"<option>DSUM (database, field, criteria) </option>"+
"<option>DVAR (database, field, criteria) </option>"+
"<option>DVARP (database, field, criteria) </option>"+
"<option>MATCH (lookupValue, lookupArray, matchType) </option>"+
"<option>BESSELI (x, n) </option>"+
"<option>BESSELJ (x, n) </option>"+
"<option>BESSELK (x, n) </option>"+
"<option>BESSELY (x, n) </option>"+
"<option>BIN2DEC (number) </option>"+
"<option>BIN2HEX (number, places) </option>"+
"<option>BIN2OCT (number, places) </option>"+
"<option>BITAND (number1, number2) </option>"+
"<option>BITLSHIFT (number, shift) </option>"+
"<option>BITOR (number1, number2) </option>"+
"<option>BITRSHIFT (number, shift) </option>"+
"<option>BITXOR (number1, number2) </option>"+
"<option>COMPLEX (real, imaginary, suffix) </option>"+
"<option>CONVERT (number, from_unit, to_unit) </option>"+
"<option>DEC2BIN (number, places) </option>"+
"<option>DEC2HEX (number, places) </option>"+
"<option>DEC2OCT (number, places) </option>"+
"<option>DELTA (number1, number2) </option>"+
"<option>ERF (lower_bound, upper_bound) </option>"+
"<option>ERF.PRECISE () </option>"+
"<option>ERFC (x) </option>"+
"<option>ERFC.PRECISE () </option>"+
"<option>GESTEP (number, step) </option>"+
"<option>HEX2BIN (number, places) </option>"+
"<option>HEX2DEC (number) </option>"+
"<option>HEX2OCT (number, places) </option>"+
"<option>IMABS (inumber) </option>"+
"<option>IMAGINARY (inumber) </option>"+
"<option>IMARGUMENT (inumber) </option>"+
"<option>IMCONJUGATE (inumber) </option>"+
"<option>IMCOS (inumber) </option>"+
"<option>IMCOSH (inumber) </option>"+
"<option>IMCOT (inumber) </option>"+
"<option>IMDIV (inumber1, inumber2) </option>"+
"<option>IMEXP (inumber) </option>"+
"<option>IMLN (inumber) </option>"+
"<option>IMLOG10 (inumber) </option>"+
"<option>IMLOG2 (inumber) </option>"+
"<option>IMPOWER (inumber, number) </option>"+
"<option>IMPRODUCT () </option>"+
"<option>IMREAL (inumber) </option>"+
"<option>IMSEC (inumber) </option>"+
"<option>IMSECH (inumber) </option>"+
"<option>IMSIN (inumber) </option>"+
"<option>IMSINH (inumber) </option>"+
"<option>IMSQRT (inumber) </option>"+
"<option>IMCSC (inumber) </option>"+
"<option>IMCSCH (inumber) </option>"+
"<option>IMSUB (inumber1, inumber2) </option>"+
"<option>IMSUM () </option>"+
"<option>IMTAN (inumber) </option>"+
"<option>OCT2BIN (number, places) </option>"+
"<option>OCT2DEC (number) </option>"+
"<option>OCT2HEX (number, places) </option>"+
"<option>ACCRINT (issue, first, settlement, rate, par, frequency, basis) </option>"+
"<option>ACCRINTM null;</option>"+
"<option>AMORDEGRC null;</option>"+
"<option>AMORLINC null;</option>"+
"<option>COUPDAYBS null;</option>"+
"<option>COUPDAYS null;</option>"+
"<option>COUPDAYSNC null;</option>"+
"<option>COUPNCD null;</option>"+
"<option>COUPNUM null;</option>"+
"<option>COUPPCD null;</option>"+
"<option>CUMIPMT (rate, periods, value, start, end, type) </option>"+
"<option>CUMPRINC (rate, periods, value, start, end, type) </option>"+
"<option>DB (cost, salvage, life, period, month) </option>"+
"<option>DDB (cost, salvage, life, period, factor) </option>"+
"<option>DISC null;</option>"+
"<option>DOLLARDE (dollar, fraction) </option>"+
"<option>DOLLARFR (dollar, fraction) </option>"+
"<option>DURATION null;</option>"+
"<option>EFFECT (rate, periods) </option>"+
"<option>FV (rate, periods, payment, value, type) </option>"+
"<option>FVSCHEDULE (principal, schedule) </option>"+
"<option>INTRATE null;</option>"+
"<option>IPMT (rate, period, periods, present, future, type) </option>"+
"<option>IRR (values, guess) </option>"+
"<option>ISPMT (rate, period, periods, value) </option>"+
"<option>MDURATION null;</option>"+
"<option>MIRR (values, finance_rate, reinvest_rate) </option>"+
"<option>NOMINAL (rate, periods) </option>"+
"<option>NPER (rate, payment, present, future, type) </option>"+
"<option>NPV () </option>"+
"<option>ODDFPRICE null;</option>"+
"<option>ODDFYIELD null;</option>"+
"<option>ODDLPRICE null;</option>"+
"<option>ODDLYIELD null;</option>"+
"<option>PDURATION (rate, present, future) </option>"+
"<option>PMT (rate, periods, present, future, type) </option>"+
"<option>PPMT (rate, period, periods, present, future, type) </option>"+
"<option>PRICE null;</option>"+
"<option>PRICEDISC null;</option>"+
"<option>PRICEMAT null;</option>"+
"<option>PV (rate, periods, payment, future, type) </option>"+
"<option>RATE (periods, payment, present, future, type, guess) </option>"+
"<option>RECEIVED null;</option>"+
"<option>RRI (periods, present, future) </option>"+
"<option>SLN (cost, salvage, life) </option>"+
"<option>SYD (cost, salvage, life, period) </option>"+
"<option>TBILLEQ (settlement, maturity, discount) </option>"+
"<option>TBILLPRICE (settlement, maturity, discount) </option>"+
"<option>TBILLYIELD (settlement, maturity, price) </option>"+
"<option>VDB null;</option>"+
"<option>XIRR (values, dates, guess) </option>"+
"<option>XNPV (rate, values, dates) </option>"+
"<option>YIELD null;</option>"+
"<option>YIELDDISC null;</option>"+
"<option>YIELDMAT null;</option>"+
"<option>CELL null;</option>"+
"<option>ERROR ;</option>"+
"<option>ERROR.TYPE (error_val) </option>"+
"<option>INFO null;</option>"+
"<option>ISBLANK (value) </option>"+
"<option>ISBINARY (number) </option>"+
"<option>ISERR (value) </option>"+
"<option>ISERROR (value) </option>"+
"<option>ISEVEN (number) </option>"+
"<option>ISFORMULA null;</option>"+
"<option>ISLOGICAL (value) </option>"+
"<option>ISNA (value) </option>"+
"<option>ISNONTEXT (value) </option>"+
"<option>ISNUMBER (value) </option>"+
"<option>ISODD (number) </option>"+
"<option>ISREF null;</option>"+
"<option>ISTEXT (value) </option>"+
"<option>N (value) </option>"+
"<option>NA () </option>"+
"<option>SHEET null;</option>"+
"<option>SHEETS null;</option>"+
"<option>TYPE (value) </option>"+
"<option>AND () </option>"+
"<option>CHOOSE () </option>"+
"<option>FALSE () </option>"+
"<option>IF (test, then_value, otherwise_value) </option>"+
"<option>IFERROR (value, valueIfError) </option>"+
"<option>IFNA (value, value_if_na) </option>"+
"<option>NOT (logical) </option>"+
"<option>OR () </option>"+
"<option>TRUE () </option>"+
"<option>XOR () </option>"+
"<option>SWITCH () </option>"+
"<option>ABS (number) </option>"+
"<option>ACOS (number) </option>"+
"<option>ACOSH (number) </option>"+
"<option>ACOT (number) </option>"+
"<option>ACOTH (number) </option>"+
"<option>AGGREGATE null</option>"+
"<option>ARABIC (text) </option>"+
"<option>ASIN (number) </option>"+
"<option>ASINH (number) </option>"+
"<option>ATAN (number) </option>"+
"<option>ATAN2 (number_x, number_y) </option>"+
"<option>ATANH (number) </option>"+
"<option>BASE (number, radix, min_length) </option>"+
"<option>CEILING (number, significance, mode) </option>"+
"<option>CEILING.MATH CEILING;</option>"+
"<option>CEILING.PRECISE CEILING;</option>"+
"<option>COMBIN (number, number_chosen) </option>"+
"<option>COMBINA (number, number_chosen) </option>"+
"<option>COS (number) </option>"+
"<option>COSH (number) </option>"+
"<option>COT (number) </option>"+
"<option>COTH (number) </option>"+
"<option>CSC (number) </option>"+
"<option>CSCH (number) </option>"+
"<option>DECIMAL (number, radix) </option>"+
"<option>DEGREES (number) </option>"+
"<option>EVEN (number) </option>"+
"<option>EXP Math.exp;</option>"+
"<option>FACT (number) </option>"+
"<option>FACTDOUBLE (number) </option>"+
"<option>FLOOR (number, significance, mode) </option>"+
"<option>FLOOR.MATH FLOOR;</option>"+
"<option>GCD null;</option>"+
"<option>INT (number) </option>"+
"<option>LCM () </option>"+
"<option>LN (number) </option>"+
"<option>LOG (number, base) </option>"+
"<option>LOG10 (number) </option>"+
"<option>MDETERM null;</option>"+
"<option>MINVERSE null;</option>"+
"<option>MMULT null;</option>"+
"<option>MOD (dividend, divisor) </option>"+
"<option>MROUND (number, multiple) </option>"+
"<option>MULTINOMIAL () </option>"+
"<option>MUNIT null;</option>"+
"<option>ODD (number) </option>"+
"<option>PI () </option>"+
"<option>POWER (number, power) </option>"+
"<option>PRODUCT () </option>"+
"<option>QUOTIENT (numerator, denominator) </option>"+
"<option>RADIANS (number) </option>"+
"<option>RAND () </option>"+
"<option>RANDBETWEEN (bottom, top) </option>"+
"<option>ROMAN null;</option>"+
"<option>ROUND (number, digits) </option>"+
"<option>ROUNDDOWN (number, digits) </option>"+
"<option>ROUNDUP (number, digits) </option>"+
"<option>SEC (number) </option>"+
"<option>SECH (number) </option>"+
"<option>SERIESSUM (x, n, m, coefficients) </option>"+
"<option>SIGN (number) </option>"+
"<option>SIN (number) </option>"+
"<option>SINH (number) </option>"+
"<option>SQRT (number) </option>"+
"<option>SQRTPI (number) </option>"+
"<option>SUBTOTAL null;</option>"+
"<option>ADD (num1, num2) </option>"+
"<option>MINUS (num1, num2) </option>"+
"<option>DIVIDE (dividend, divisor) </option>"+
"<option>MULTIPLY (factor1, factor2) </option>"+
"<option>GTE (num1, num2) </option>"+
"<option>LT (num1, num2) </option>"+
"<option>LTE (num1, num2) </option>"+
"<option>EQ (value1, value2) </option>"+
"<option>NE (value1, value2) </option>"+
"<option>POW (base, exponent) </option>"+
"<option>SUM () </option>"+
"<option>SUMIF (range, criteria) </option>"+
"<option>SUMIFS () </option>"+
"<option>SUMPRODUCT null;</option>"+
"<option>SUMSQ () </option>"+
"<option>SUMX2MY2 (array_x, array_y) </option>"+
"<option>SUMX2PY2 (array_x, array_y) </option>"+
"<option>SUMXMY2 (array_x, array_y) </option>"+
"<option>TAN (number) </option>"+
"<option>TANH (number) </option>"+
"<option>TRUNC (number, digits) </option>"+
"<option>UNIQUE () </option>"+
"<option>FLATTEN utils.flatten;</option>"+
"<option>ARGS2ARRAY () </option>"+
"<option>REFERENCE (context, reference) </option>"+
"<option>JOIN (array, separator) </option>"+
"<option>NUMBERS () </option>"+
"<option>NUMERAL null;</option>"+
"<option>ASC null;</option>"+
"<option>BAHTTEXT null;</option>"+
"<option>CHAR (number) </option>"+
"<option>CLEAN (text) </option>"+
"<option>CODE (text) </option>"+
"<option>CONCATENATE () </option>"+
"<option>DBCS null;</option>"+
"<option>DOLLAR null;</option>"+
"<option>EXACT (text1, text2) </option>"+
"<option>FIND (find_text, within_text, position) </option>"+
"<option>FIXED null;</option>"+
"<option>HTML2TEXT (value) </option>"+
"<option>LEFT (text, number) </option>"+
"<option>LEN (text) </option>"+
"<option>LOWER (text) </option>"+
"<option>MID (text, start, number) </option>"+
"<option>NUMBERVALUE null;</option>"+
"<option>PRONETIC null;</option>"+
"<option>PROPER (text) </option>"+
"<option>REGEXEXTRACT (text, regular_expression) </option>"+
"<option>REGEXMATCH (text, regular_expression, full) </option>"+
"<option>REGEXREPLACE (text, regular_expression, replacement) </option>"+
"<option>REPLACE (text, position, length, new_text) </option>"+
"<option>REPT (text, number) </option>"+
"<option>RIGHT (text, number) </option>"+
"<option>SEARCH (find_text, within_text, position) </option>"+
"<option>SPLIT (text, separator) </option>"+
"<option>SUBSTITUTE (text, old_text, new_text, occurrence) </option>"+
"<option>T (value) </option>"+
"<option>TEXT null;</option>"+
"<option>TRIM (text) </option>"+
"<option>UNICHAR CHAR;</option>"+
"<option>UNICODE CODE;</option>"+
"<option>UPPER (text) </option>"+
"<option>VALUE null;</option>"+
"<option>AVEDEV null;</option>"+
"<option>AVERAGE () </option>"+
"<option>AVERAGEA () </option>"+
"<option>AVERAGEIF (range, criteria, average_range) </option>"+
"<option>AVERAGEIFS null;</option>"+
"<option>COUNT () </option>"+
"<option>COUNTA () </option>"+
"<option>COUNTIN (range, value) </option>"+
"<option>COUNTBLANK () </option>"+
"<option>COUNTIF (range, criteria) </option>"+
"<option>COUNTIFS () </option>"+
"<option>COUNTUNIQUE () </option>"+
"<option>FISHER (x) </option>"+
"<option>FISHERINV (y) </option>"+
"<option>FREQUENCY (data, bins) </option>"+
"<option>LARGE (range, k) </option>"+
"<option>MAX () </option>"+
"<option>MAXA () </option>"+
"<option>MIN () </option>"+
"<option>MINA () </option>"+
"<option>MODE ;</option>"+
"<option>MODE.MULT () </option>"+
"<option>MODE.SNGL () </option>"+
"<option>PERCENTILE ;</option>"+
"<option>PERCENTILE.EXC (array, k) </option>"+
"<option>PERCENTILE.INC (array, k) </option>"+
"<option>PERCENTRANK ;</option>"+
"<option>PERCENTRANK.EXC (array, x, significance) </option>"+
"<option>PERCENTRANK.INC (array, x, significance) </option>"+
"<option>PERMUT (number, number_chosen) </option>"+
"<option>PERMUTATIONA (number, number_chosen) </option>"+
"<option>PHI (x) </option>"+
"<option>PROB (range, probability, lower, upper) </option>"+
"<option>QUARTILE ;</option>"+
"<option>QUARTILE.EXC (range, quart) </option>"+
"<option>QUARTILE.INC (range, quart) </option>"+
"<option>RANK ;</option>"+
"<option>RANK.AVG (number, range, order) </option>"+
"<option>RANK.EQ (number, range, order) </option>"+
"<option>RSQ (data_x, data_y) // no need to flatten here, PEARSON will take care of that</option>"+
"<option>SMALL (range, k) </option>"+
"<option>STANDARDIZE (x, mean, sd) </option>"+
"<option>STDEV ;</option>"+
"<option>STDEV.P () </option>"+
"<option>STDEV.S () </option>"+
"<option>STDEVA () </option>"+
"<option>STDEVPA () </option>"+
"<option>VAR ;</option>"+
"<option>VAR.P () </option>"+
"<option>VAR.S () </option>"+
"<option>VARA () </option>"+
"<option>VARPA () </option>"+
"<option>WEIBULL ;</option>"+
"<option>WEIBULL.DIST (x, alpha, beta, cumulative) </option>"+
"<option>Z ;</option>"+
"<option>Z.TEST (range, x, sd) </option>"+
"<option>PROGRESS (p, c) </option>"+
"<option>RATING (v) </option>";
</script>
</body>
</html>
Use x.setValueFromCoords( COL_FROM_0, ROW_FROM_0, <YOUR_VALUE>,TRUE ), then send table with JSON.stringify(x.getData())