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); } }