[心得] ai2+google sheet script

function doGet(e) { 
  return Authentication(e)
}
function doPost(e) { 
  return Authentication(e)
}


function  Authentication(e) {
  
  
  var ss = SpreadsheetApp.openById("ID");

  var sh = ss.getSheetByName('標籤');
  
  if (e.parameter.func=="Create"){
        
   
   
   

     var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
     var email = e.parameter.email;
     var sql = '"Select B where B=\''+email+'\'"'; 
     var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';
    
    var ts = ss.insertSheet();   
    var setQuery = ts.getRange(1,1).setFormula(qry)
    var getResult = ts.getDataRange().getValues();
     ss.deleteSheet(ts);

     
   
   
      
      
       if (getResult==""){
         var data =[e.parameter.id, e.parameter.email, e.parameter.pwd];
         sh.appendRow(data);
  
      return ContentService.createTextOutput("GOOD");
    
     } else { return ContentService.createTextOutput("ggg");

            }
     

}
//--------------------------------------------------------------------------


   
    
      if (e.parameter.func=="Login"){


     
     var email = e.parameter.email;

     var password = e.parameter.pwd;
     
     var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
     
      var sql = '"Select A,B,C where B=\''+email+'\'"';
       

     
      var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';

     var ts = ss.insertSheet();

     var setQuery = ts.getRange(1,1).setFormula(qry);

     var getResult = ts.getDataRange().getValues();

     var getPWD = ts.getRange(1,3).getValues();

     var getFullName = ts.getRange(1,1).getValues();

     ss.deleteSheet(ts);

     if (getResult!=""){


       if (getPWD==password){return ContentService.createTextOutput(getFullName);}

       else { return ContentService.createTextOutput("通行止め！");}
     }

     else{return ContentService.createTextOutput("お前は誰？");}

   }

//--------------------------------------------------------------------------
  
  
   if (e.parameter.func=="SendEmail"){
        
   
   
   
     var email = e.parameter.email;
     var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
     var sql = '"Select A,B,C where B=\''+email+'\'"'; 
     var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';
    
     var ts = ss.insertSheet();   
     var setQuery = ts.getRange(1,1).setFormula(qry)
     var getResult = ts.getDataRange().getValues();
     var getPWD = ts.getRange(1,3).getValues();
     var anata = 'あなたのパスワードは';
     var desu = 'です';
     ss.deleteSheet(ts);
    
 
       if (getResult!=="")
       {GmailApp.sendEmail(''+email+'','ログインアカウントパスワードのご案内',''+anata+''+getPWD+''+desu+'');}
         
    
         else{return ContentService.createTextOutput(getResult);}

            }
     

* GOOGLE 試算表 欄位請換成純文字，否則會出問題