I don’t know how to update a cell when its already created. I thought that it would work fine but it didn’t. Any thoughts?
Blocks for updating:
I don’t know how to update a cell when its already created. I thought that it would work fine but it didn’t. Any thoughts?
Blocks for updating:
Can you paste your code google script in here?, so i can try your script
function doGet(e) {
return Authentication(e);
}
function doPost(e) {
return Authentication(e);
}
function Authentication(e) {
if (e.parameter.func == “Create”) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var rg = sh.getName() + “!” + sh.getDataRange().getA1Notation();
var username = e.parameter.username;
var sql = ‘"Select B where B=’’+username+’’"’;
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.name, e.parameter.username, e.parameter.password, e.parameter.counter];
sh.appendRow(data);
return ContentService.createTextOutput(getResult);}
}
//------------------------------------------------------
if (e.parameter.func == “Login”) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var username = e.parameter.username;
var password = e.parameter.password;
var rg = sh.getName() + “!” + sh.getDataRange().getA1Notation();
var sql = ‘"Select A,B,C,D where B=’’+username+’’"’;
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();
var getCounter = ts.getRange(1,4).getValues();
ss.deleteSheet(ts);
if (getResult!=""){
if (getPWD==password){
return ContentService.createTextOutput(getFullName);
}
else { return ContentService.createTextOutput(“ERPWD”);}
}
else{return ContentService.createTextOutput(getResult);}
}
//---------------------------------------------------------
if (e.parameter.func == “Counter”) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var username = e.parameter.username;
var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = '"Select A,B,C,D where B=\''+username+'\'"';
var qry = '=IFERROR(query(' + rg + ',' + sql + '),"")';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
var getCounter = ts.getRange(1,4).getValues();
ss.deleteSheet(ts);
if (getResult!=""){
return ContentService.createTextOutput(getCounter);
} else { return ContentService.createTextOutput(“ERCOUNTER”);}
}
//---------------------------------------------------------------
if (e.parameter.func == “Counter2”) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var rg = sh.getName() + “!” + sh.getDataRange().getA1Notation();
var username = e.parameter.username;
var counter = e.parameter.counter;
var sql = ‘"Select A,B,C,D where B=’’+username+’’"’;
var qry = ‘=IFERROR(query(’ + rg + ‘,’ + sql + ‘),"")’;
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult2 = ts.getDataRange().getValues();
var getCounter2 = ts.getRange(1,4).getValues();
ss.deleteSheet(ts);
if (getResult2!=""){
getCounter2.setValues(counter);
//return ContentService.createTextOutput(getResult2);
}
}
}
//var update_todays_cells = stats_sheet.getRange(“A3:D3”);
//update_todays_cells.setValues(todays_cells);
You can change like this
var sql = “Select A,B,C,D where B=’”+username+"’";
var qry = ‘=IFERROR(query(’ + rg + ‘,"’ + sql + ‘"),"")’;
You must make sure the username is blue
it didnt work sadly
To do an update in your script:
Assumes:
Example url - for a doGet(e):
https://<webappurl>/exec?fn=Update&tag=Bob&value=12345
if (e.parameter.fn == 'Update') {
var ss = SpreadsheetApp.openById('Your Sheet ID');
var sh = ss.getSheetByName('SheetName');
var rng = sh.getDataRange();
var data = rng.getValues();
for (var i = 0; i < data.length; i++) {
if (e.parameter.tag == data[i][0]) {
sh.getRange((i+1),2,1,1).setValue(e.parameter.value);
}
}
msg = 'Tag Updated';
}
I managed to solve it by myself, thanks everyone for your suggestions
In which case please share your solution so others may benefit…