1

I'm trying to create a basic time clock web app.

So far, I'm using this script to create this web app which takes the input values and puts them in this spreadsheet for the time stamping part.

I need it to use one of the values from the form and perform a lookup in this sheet (take the longId and find me the name) and return the (name) value to the html page as a verification for the end user that they were identified correctly. Unfortunately, I don't know enough to grasp what I'm doing wrong. Let me know if I need to provide more info.

Edit 1

I'm thinking that I wasn't clear enough. I don't need the user info from entry, I need the user from a lookup. The user will be entering their ID anonymously, I need to match the ID to their info, and bring the info back for them to verify.

Edit 2

Using the link provided by Br. Sayan, I've created this script using this spreadsheet as above to test one piece of this. The web app here spits out: undefined. It should spit out "Student 3" Still not sure what I'm doing wrong.

Sean Dooley
  • 129
  • 1
  • 6
  • Oops. That's still left over from testing. Deleted. It should trigger with the form "submit" input labeled "Next...". You should be able to see it in the Javascript.html file. – Sean Dooley Feb 22 '16 at 15:09
  • You can do away with outer `[ ]` in `student.push([data[i][0]]);` I suppose. Shouldn't it be `student.push(data[i][0]);` And also please let's know what does, `Logger.log(data[i][0])` say? Put it right after `Logger.log("yes")`. Otherwise we need to use the `console.log()` to see what's going on. – Br. Sayan Feb 23 '16 at 03:05

3 Answers3

1

Please try this one:

Fetch Data from Google Spread Sheet
(source: technokarak.com)

Also please have a look at:

Retrieve rows from spreadsheet data using GAS

EDIT:

Please make these changes in your function and let us know.

function findValue() {
  var data =        SpreadsheetApp.openById("15DRZRQ2Hcd7MNnAsu_lnZ6n4kiHeXW_OMPP3squbTLE").getSheetByName("Volatile Data").getDataRange().getValues();
  for(i in data) {
    if(data[i][3] == 100000003) {
      Logger.log("yes");
      Logger.log(data[i][0]);
      var student = [];
      student.push(data[i][0]);
      return student;
    }
  }
}
Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Br. Sayan
  • 486
  • 1
  • 7
  • 22
  • You can do away with outer `[ ]` in `student.push([data[i][0]]);`I suppose. Shouldn't it be `student.push(data[i][0]);` And also please let's know what does, `Logger.log(data[i][0])` say? Put it after `Logger.log("yes")`. Otherwise we need to use the `console.log()` to see what's going on. – Br. Sayan Feb 23 '16 at 03:01
  • I've edited the function a little in my answer itself. Please try and let me know. – Br. Sayan Feb 23 '16 at 03:11
  • As Bryan P has mentioned , you run the `google.script.run` with the event i.e mouseClick or whatsoever and in the function in `Code.gs` simply do a `return student`, which you already have. Only replace the `.findStudent(value);` with `.findValue(SpreadsheetId)`. Now your `Index.html` and `Code.gs` are not matching. `value` - which you're passing to the function contains nothing as such. Please check and make the changes and let me know. :) – Br. Sayan Feb 23 '16 at 04:18
  • See question edit. Unfortunately now all IDs are returning as "Student 1". I added a few console logs in an attempt to help. – Sean Dooley Feb 23 '16 at 06:22
  • Don't need console.log(). Post the Logger.log() entries please. – Br. Sayan Feb 23 '16 at 06:30
  • Also add a `Logger.log('i : ' + i);` to get the value for `i` also, before `Logger.log("Match Found");` – Br. Sayan Feb 23 '16 at 06:36
  • Found the problem - I had ===, needed ==. All looks good for now. Will do some more testing and update. – Sean Dooley Feb 23 '16 at 06:38
1

One way for the next button to grab the student input field:

<input type="submit" onclick="studentName(document.getElementById('student').value)" value="Next..."/>

That sends the value to this func in Javascript.html:

function studentName(value) {
  google.script.run
  .withSuccessHandler(findSuccess)
  .findStudent(value);
}

Which sends it to a findStudent(value) in Code.gs You do the lookup and the return value goes back to findSuccess( result ) back in Javascript.html. Handle the result from there.

Also consider keeping the stock preventDefault() code that comes with the Web App template in the Help > Welcome Screen.

Bryan P
  • 5,031
  • 3
  • 30
  • 44
  • I'm going to look into this one thank you! And - whoa on the Web App template. I've never used that. That is a lot of info to take in! That will take me a minute to wrap my head around. Thank you! – Sean Dooley Feb 22 '16 at 23:13
  • This is a huge help, and I think that I've nearly gotten it, but I'm still doing something wrong. See update in original post. – Sean Dooley Feb 23 '16 at 05:23
  • Sorry, I just figured context would be a good thing. I don't really know what I'm looking for, so I don't really know what someone who does, like you guys, would need to see. I'll attempt to simplify in the future. – Sean Dooley Feb 24 '16 at 04:20
0

It is a complicated answer, I have had a lot of success with:

function process(object){

  var user = Session.getActiveUser().getEmail();
  var key = object.Key;

  send(key);

}

function send(k){

  var ss = 
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastR = ss.GetLastRow();

  ss.GetRange(lastR,1).SetValue(k);

}

On your html button you will need to have inside the tags 

onClick="google.script.run
          .withSuccessHandler(Success)
          .process(this.parentNode);"

In order for this to work, obviously you will need to have your fields named accordingly.

Edit: The only thing I did not include in the code was a Success handler, which will be in your html of the GAS script. This should point you in a direction that can resolve that.

Hope this helps.

Andrew
  • 1