Get row data just after logging in form google sheet

I am trying to get data from google sheets just after login. my sheet is this way column A has user name column B has password and column C has a unique id which will be used in a different screen for Arduino use. I I just need help to get back the unique id for a specific username and password.
Screen Shot 2022-11-30 at 3.04.37 PM

my google script code is :

function doGet(e) {
var message = "";
var ss = SpreadsheetApp.openById(e.parameter.sheetId);
var sh = ss.getSheetByName(e.parameter.gridName);
var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = e.parameter.query;
var qry = '=query(' + rg + ',"' + sql + '",0)';

var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();

if (getResult[0][0] == "#N/A") {
message = 'username not present';
} else {
if (getResult[0][1] != e.parameter.pwd) {
message = 'incorrect password';
} else {
message = 'signed in';
return ContentService.createTextOutput(message);

I am a newbie to google sheets

You can just use gviz query for this (if sheet has permissions for anyone with the link):

SELECT C WHERE A = username AND B = password

If your google sheet is private/restricted, then you will need a google apps script

You should not really store passwords in plain text if you are concerned about security

thanks TIMAI2 these are not passwords set by the user this password is set by me to give the users a limited time access. how can I return the data back to the App and what changes do I have to make in the app

No need to change in the script code . Just use gviz method of query. Web component alone is enough. Look at here (credit @TIMAI2 )

google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"invalid_query","message":"INVALID_QUERY","detailed_message":"Invalid query: NO_COLUMN: john"}]});

this is what I am recieving

Which query did you send?
Column names are letter like A or B


Also note that because you do not have a header row, the gviz query may return the first row (as a header row), along with your query output.

SELECT C WHERE A = username AND B = password
I ran this in the app

The name Tim must be 'Tim'
You have forget to add that symbol in prefix and suffix. No need to add such symbol for numbers but for text it must be 'sjshsh'

1 Like

thanks its done

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.