Update cell in google sheet

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:

2021-01-16 (3)

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:

  1. You have two columns (name and password)
  2. You send the parameters “tag” = name and “value” = password
  3. You want to change the password!

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 :slight_smile:

In which case please share your solution so others may benefit…