HI TIMAI2,I CAN'T UNDERSTAND IT GIVES ME AN ERROR WHEN I SEND TO CHANGE A PERSON'S TURN.
CAN YOU HELP ME UNDERSTAND IN MY FILE.AIA WHERE I AM WRONG
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
// enter row number and column number to return A1 Notation Cell reference
if ( fn == 'getCellRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter start and end row numbers and column numbers to return A1 Notation Range reference
else if ( fn == 'getRangeRef' ) {
var ref = sh.getRange(e.parameter.ROW,e.parameter.COL,e.parameter.ROW1,e.parameter.COL1).getA1Notation();
return ContentService.createTextOutput(ref);
}
// enter A1 notation reference to return cell value
else if ( fn == 'readCell' ) {
var ref = sh.getRange(e.parameter.REF).getValue();
return ContentService.createTextOutput(ref);
}
// enter row number to return all values in row as a list
else if ( fn == 'readRow' ) {
var ref = sh.getRange(parseInt(e.parameter.ROW),1,1,rg[0].length).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter column number (A=1/B=2/etc.) to return all values in column as a list
else if ( fn == 'readCol' ) {
var ref = sh.getRange(2,parseInt(e.parameter.COL),rg.length-1,1).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// enter A1 notation reference to return range values as a list
else if ( fn == 'readRange' ) {
var ref = sh.getRange(e.parameter.REF).getValues();
return ContentService.createTextOutput(JSON.stringify(ref));
}
// returns all values on sheet, including headers
else if ( fn == 'readSheet' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}
// outputs results for SQL query of all data
else if ( fn == 'querySheet' ) {
var sql = e.parameter.SQL;
var hdr = e.parameter.HDR;
if ( hdr == 0 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation().replace('A1','A2');
var qry = '=query(' + rgq + ';\"' + sql + '\";0)';
} else if ( hdr == 1 ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
}
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}
// enter row number to delete that row
else if ( fn == 'deleteRow' ) {
sh.deleteRow(parseInt(e.parameter.ROW));
return ContentService.createTextOutput('Row ' + e.parameter.ROW + ' has been deleted');
}
// enter row number to delete that row
else if ( fn == 'deleteCol' ) {
sh.deleteColumn(parseInt(e.parameter.COL));
return ContentService.createTextOutput('Column ' + e.parameter.COL + ' has been deleted');
}
// enter A1 notation reference to write cell value
else if ( fn == 'writeCell' ) {
sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Value: ' + e.parameter.DATA + ' written to cell ' + e.parameter.REF);
}
// enter row number to write row values
else if ( fn == 'writeRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
sh.getRange(e.parameter.ROW,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to row ' + e.parameter.ROW);
}
// enter row number to write row values
else if ( fn == 'writeCol' ) {
var data = JSON.parse(e.parameter.DATA);
sh.getRange(1,e.parameter.COL,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to column ' + e.parameter.COL);
}
// enter A1 notation reference to write cell values
else if ( fn == 'writeRange' ) {
var data = JSON.parse(e.parameter.DATA);
var ref = sh.getRange(e.parameter.REF).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' written to cells ' + e.parameter.REF);
}
// append row to end write row values
else if ( fn == 'appendRow' ) {
var data = JSON.parse('[' + e.parameter.DATA + ']');
var lr = sh.getLastRow();
sh.insertRowAfter(lr);
rowNum = lr + 1;
sh.getRange(rowNum,1,1,data[0].length).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
}
// append column to end and write column values
else if ( fn == 'appendCol' ) {
var data = JSON.parse(e.parameter.DATA);
var lc = sh.getLastColumn();
sh.insertColumnAfter(lc);
colNum = lc + 1;
sh.getRange(1,colNum,data.length,1).setValues(data);
return ContentService.createTextOutput('Values: ' + e.parameter.DATA + ' appended');
}
// enter A1 notation reference and formula to set formula to cell
else if ( fn == 'insertFormula' ) {
var ref = sh.getRange(e.parameter.REF).setValue(e.parameter.DATA);
return ContentService.createTextOutput('Formula: ' + e.parameter.DATA + ' inserted to ' + e.parameter.REF);
}
}