//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)
},

};