0.0.0 • Published 12 months ago
@theracerx/custom-google-crud v0.0.0
//for GET HTTP method // can be GET only // for customer viewing //return ContentService.createTextOutput is only sent back to requestor in this function!!! //never use ASYNC function doGet(e) { Logger.log("doGet started");
let res; //TEST GET sheet //res = GET.query({sheet:ss.getSheetByName("Sheet1")}) //return Sheet
//TEST GET row //res = GET.query({sheet:ss.getSheetByName("Sheet1"),rowPos:1}) //return Row
//TEST GET column //res = GET.query({sheet:ss.getSheetByName("Sheet1"),colPos:1}) //return Column
//TEST GET cell //res = GET.query({sheet:ss.getSheetByName("Sheet1"),colPos:1,rowPos:1}) //return Cell
//TEST GET a1notation //res = GET.query({sheet:ss.getSheetByName("Sheet1"),a1notation:"A1"}) //return Cell
//TEST GET r1c1notation //res = GET.query({sheet:ss.getSheetByName("Sheet1"),r1c1notation:"A1:B4"}) //return Range
//TEST GET find matching columnVal given rowVal //res = GET.query({sheet:ss.getSheetByName("Sheet1"),field1:"Link",field2:"Year",rowVal:"asdf"}) //missing fiedrowpos //res = GET.query({sheet:ss.getSheetByName("Sheet1"),field1:"Year",field2:"Link",fieldRowPos:1,rowVal:"2024"}) //searches to row1 //res = GET.query({sheet:ss.getSheetByName("Sheet1"),field1:"Link",field2:"Year",fieldRowPos:1,rowVal:"asdf"}) //searches to row1
Logger.log(res) if(res == "TextOutput"){ return res } else return MISC.sendStatus(true,res) }
//for POST HTTP method // can be PATCH, POST, or DELETE // for editors only function doPost(e) { Logger.log("doPost started");
//TEST POST sheet
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),values:[["A","B","C"]]}) //will Error
//TEST POST row
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),rowPos:2,values:[["A","B","C"]],length:3}) //return Row
//TEST POST column
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),colPos:2,values:[["A"],["B"],["C"]],length:3}) //return Column
//TEST POST cell
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),colPos:3,rowPos:3,value:"AAA"}) //return Cell
//TEST POST a1notation
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),a1notation:"A1",value:"TEST"}) //return Cell
//TEST POST r1c1notation
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),r1c1notation:"A1:B2",values:[[1,2],[1,2]]}) //return Range
//TEST POST find matching columnVal given rowVal
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),field1:"Link",field2:"Year",rowVal:"asdf"}) //missing fiedrowpos
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),field1:"a",field2:"b",fieldRowPos:1,rowVal:"a1",value:"GGG"}) //searches to row1
//res = POST.setValue({sheet:ss.getSheetByName("Sheet2"),field1:"Link",field2:"Year",fieldRowPos:1,rowVal:"asdf"},true) //searches to row1}
var ACTION = {} //add your custom actions here
var GET = {//default GET
//QUERY commands--------------------------------------------
//length is only used by POST & PATCH for querying columns and rows
query:({sheet,rowPos,colPos,rowVal,colVal,field1,field2,fieldRowPos,a1notation,r1c1notation,length},returnSrc)=>{ //shorthand command
Logger.log("query started")
//min req a1notation
if(a1notation){
return GET.queryA1Notation({sheet:sheet,a1notation:a1notation},returnSrc)
}
//min req r1c1notation
if(r1c1notation){
return GET.queryR1C1Notation({sheet:sheet,r1c1notation:r1c1notation},returnSrc)
}
// min req rowPos, colPos
if(!rowPos && colPos){ //returns column
return GET.queryColumn({sheet:sheet,colPos:colPos,length:length},returnSrc)
} else if(!colPos && rowPos){ //returns row
return GET.queryRow({sheet:sheet,rowPos:rowPos,length:length},returnSrc)
} else if(rowPos && colPos){//returns value at x,y
return GET.queryCell({sheet:sheet,rowPos:rowPos,colPos},returnSrc)
}
// min req field1, field2, fieldRowPos, colVal | rowVal
//give X1,Y1,Y2 find X2 (i.e. X1 = 3; Y1 = ID; Y2 = NAME; then X2 must be Pig)
if(field1 && field2){ //return matching value given colVal1
//colPos, colVal,
return GET.queryMatchCell({sheet:sheet,rowVal:rowVal,colVal:colVal,field1:field1,field2:field2,fieldRowPos:fieldRowPos},returnSrc)
} else if (!field1 && field2){ //error
return MISC.sendStatus(false,"cannot query with unknown field2")
} else if (field1 && !field2){ //error
return MISC.sendStatus(false,"cannot query with unknown field2")
}
return GET.querySheet({sheet:sheet},returnSrc)
},
//QUERY SUPPORT COMMAND
queryCell:({sheet,rowPos,colPos},returnSrc)=>{
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if (!rowPos) {
return MISC.sendStatus(false, "cannot queryCell unknown `rowPos`");
}
if (!colPos) {
return MISC.sendStatus(false, "cannot queryCell unknown `colPos`");
}
if(!returnSrc){return sheet.getRange(rowPos,colPos).getValue()
} else return sheet.getRange(rowPos,colPos)
},
queryMatchCell:({sheet,rowVal,colVal,field1,field2,fieldRowPos},returnSrc)=>{
let findMatch = ()=>{ //returns query with rowPos colPos
if(!fieldRowPos)return MISC.sendStatus(false,"cannot query with fieldRowPos = undefined")
//Logger.log("fieldRowPos:" + fieldRowPos)
//Logger.log("rowVal:" + rowVal)
//Logger.log("colVal:" + colVal)
//Logger.log(rowVal || colVal)
let trgtColPos1 = GET.getColPosInRow({sheet:sheet,rowPos:fieldRowPos,rowVal:field1})
Logger.log("trgtColPos1")
Logger.log(trgtColPos1 == "TextOutput")
if(trgtColPos1 == "TextOutput"){
}
let trgtRowPos = GET.getRowPosInCol({sheet:sheet,colPos:trgtColPos1,colVal:rowVal || colVal})
let trgtColPos2 = GET.getColPosInRow({sheet:sheet,rowPos:fieldRowPos,rowVal:field2})
return GET.query({sheet:sheet,rowPos:trgtRowPos,colPos:trgtColPos2},returnSrc)
}
if(rowVal && !colVal){ return findMatch()
} else if(!rowVal && colVal){ return findMatch()
} else if(rowVal && colVal && rowVal == colVal){ return findMatch()
} else if(rowVal && colVal && rowVal != colVal){
return MISC.sendStatus(false,"cannot query with both rowVal & colVal present")
} else if (!rowVal && !colVal){
return MISC.sendStatus(false,"cannot query with neither rowVal & colVal present")
}
},
getColPosInRow:({sheet,rowPos,rowVal})=>{
Logger.log("getColPosInRow started")
//verify args if present
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if (!rowPos) {
return MISC.sendStatus(false, "cannot getColPosInRow unknown `rowPos`");
}
if (!rowVal) {
return MISC.sendStatus(false, "cannot getColPosInRow unknown `rowVal`");
}
var pos = null;
var lstCol = sheet.getLastColumn();
var collection = []
for (var i = 1; i <= lstCol; i++) {
let val = sheet.getRange(rowPos, i).getValue()
if (val == rowVal) {
pos = i;
break;
} else collection.push(val)
}
if (pos == null) {
Logger.log("getColPosInRow failed: " + rowVal + " is missing from sheet:" + sheet.getName() + " ,row:" + collection)
return MISC.sendStatus(false, "cannot query rowVal: " + rowVal + " doesn't exist in sheet[" + sheet.getName() + "]");
} else {
Logger.log("getColPosInRow ended: " + pos)
return pos
}
},
getRowPosInCol:({sheet,colPos,colVal})=>{
Logger.log("getRowPosInCol started")
//verify args if present
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if (!colPos) {
return MISC.sendStatus(false, "cannot getRowPosInCol unknown `rowPos`");
}
if (!colVal) {
return MISC.sendStatus(false, "cannot getRowPosInCol unknown `rowVal`");
}
var pos = null;
var lstRow = sheet.getLastRow();
var collection = []
for (var i = 1; i <= lstRow; i++) {
let val = sheet.getRange(i, colPos).getValue()
if (val == colVal) {
pos = i;
break;
} else collection.push(val)
}
if (pos == null) {
Logger.log("getRowPosInCol failed: " + colVal + " is missing from sheet:" + sheet.getName() + " ,column:" + collection)
return MISC.sendStatus(false, "cannot query colVal: " + colVal + " doesn't exist in sheet[" + sheet.getName() + "]");
} else {
Logger.log("getRowPosInCol ended: " + pos )
return pos
}
},
queryRow:({sheet, rowPos, length},returnSrc)=>{
Logger.log("queryRow started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(!rowPos){
return MISC.sendStatus(false, "cannot queryRow unknown `rowPos`");
}
if(!returnSrc){return sheet.getRange(rowPos, 1, 1, length || sheet.getLastColumn()).getValues();
} else return sheet.getRange(rowPos, 1, 1, length || sheet.getLastColumn())
},
queryColumn:({sheet, colPos, length},returnSrc)=>{
Logger.log("queryColumn started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(!colPos){
return MISC.sendStatus(false, "cannot queryColumn unknown `colPos`");
}
if(!returnSrc){return sheet.getRange(1,colPos,length ||sheet.getLastRow(),1).getValues();
} else return sheet.getRange(1,colPos,length ||sheet.getLastRow(),1);
},
querySheet:({sheet},returnSrc)=>{
Logger.log("querySheet started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(!returnSrc){return sheet.getRange(1,1,sheet.getLastRow()|| 1,sheet.getLastColumn()|| 1).getValues();
} else return sheet.getRange(1,1,sheet.getLastRow() || 1,sheet.getLastColumn() || 1)
},
queryA1Notation:({sheet,a1notation},returnSrc)=>{
Logger.log("queryA1Notation started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(!a1notation){
return MISC.sendStatus(false, "cannot queryA1Notation unknown a1notation");
}
if(!returnSrc){return sheet.getRange(a1notation).getValue()
} else return sheet.getRange(a1notation)
},
queryR1C1Notation:({sheet,r1c1notation},returnSrc)=>{
Logger.log("queryR1C1Notation started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(!r1c1notation){
return MISC.sendStatus(false, "cannot queryR1C1Notation unknown r1c1notation");
}
if(!returnSrc){return sheet.getRange(r1c1notation).getValues()
} else return sheet.getRange(r1c1notation)
},}; var POST = {//default POST // SET VALUE Commands ---------------------------------------
//A1:C1
//[[A1,B1,C1]]
//A1:A5 like this
//[[A1],[A2],[A3],[A4],[A5]]
//And A1:C2 like this
//[[A1,B1,C1],[A2,B2,C2]]
setValue:({sheet,rowPos,colPos,rowVal,colVal,field1,field2,fieldRowPos,a1notation,r1c1notation,value,values,length})=>{
Logger.log("setValue started")
if(sheet != "Sheet"){
return MISC.sendStatus("false","cannot query sheet not instance of Sheet")
}
if(value){
if(Array.isArray(value)){
return MISC.sendStatus(false,"cannot setValue with single value instance of Array")
}
//min req a1notation
if(a1notation){
return POST.setA1notationVal({sheet:sheet,a1notation:a1notation,value:value})
}
if(rowPos && colPos){//returns value at x,y
return POST.setCellVal({sheet:sheet,rowPos:rowPos,colPos,value:value})
}
// min req field1, field2, fieldRowPos, colVal | rowVal
//give X1,Y1,Y2 find X2 (i.e. X1 = 3; Y1 = ID; Y2 = NAME; then X2 must be Pig)
if(field1 && field2){ //return matching value given colVal1
//colPos, colVal,
return POST.setMatchCellVal({sheet:sheet,rowVal:rowVal,colVal:colVal,field1:field1,field2:field2,fieldRowPos:fieldRowPos,value:value})
} else if (!field1 && field2){ //error
return MISC.sendStatus(false,"cannot setValue with unknown field2")
} else if (field1 && !field2){ //error
return MISC.sendStatus(false,"cannot setValue with unknown field2")
}
} else if (values){
if(!Array.isArray(values)){
return MISC.sendStatus(false,"cannot setValue with values not an instance of Array")
}
//min req r1c1notation
if(r1c1notation){
return POST.setR1C1NotationVal({sheet:sheet,r1c1notation:r1c1notation,values:values})
}
// min req rowPos, colPos
if(!rowPos && colPos){ //returns column
return POST.setColumnVal({sheet:sheet,colPos:colPos,values:values,length})
} else if(!colPos && rowPos){ //returns row
return POST.setRowVal({sheet:sheet,rowPos:rowPos,values:values,length})
}
return POST.setSheetVal({sheet:sheet,values:values})
} else return MISC.sendStatus(false,"cannot setValue with unknown value")
},
//setValues
setR1C1NotationVal:({sheet:sheet,r1c1notation:r1c1notation,values:values})=>{
Logger.log("setR1C1NotationVal started")
return GET.queryR1C1Notation({sheet,r1c1notation},true).setValues(values)
},
setColumnVal:({sheet,colPos,values,length})=>{//values: [[1],[2],[3]]
Logger.log("setColumnVal started")
return GET.queryColumn({sheet:sheet,colPos:colPos,length},true).setValues(values)
},
setRowVal:({sheet,rowPos,values,length})=>{ //values: [[1,2,3]]
Logger.log("setRowVal started")
return GET.queryRow({sheet:sheet,rowPos:rowPos,length:length},true).setValues(values)
},
setSheetVal:()=>{
Logger.log("setSheetVal started")
return MISC.sendStatus("false","setSheetVal doesn't work, pls see dev comment")
//obselete error
//CANNOT DO setSheetVal because empty sheet can only be [[]]
//to setSheetVal... an existing value must be present to be overwritten
//but since this is a POST method... no overwritting is permitted
//just do a series of the other command to replicate setSheetVal func
},
//setValue
setA1notationVal:({sheet,a1notation,value})=>{
Logger.log("setA1notationVal started")
return GET.queryA1Notation({sheet:sheet,a1notation:a1notation},true).setValue(value)
},
setCellVal:({sheet,rowPos,colPos,value})=>{
Logger.log("setCellVal started")
return GET.queryCell({sheet:sheet,rowPos:rowPos,colPos:colPos},true).setValue(value)
},
setMatchCellVal:({sheet,rowVal,colVal,field1,field2,fieldRowPos,value})=>{
Logger.log("setMatchCellVal started")
return GET.queryMatchCell({sheet:sheet,rowVal:rowVal,colVal:colVal,field1:field1,field2:field2,fieldRowPos:fieldRowPos},true).setValue(value)
},};
0.0.0
12 months ago