7

I want to build kind of an automatic system to update some race results for a championship. I have an automated spreadsheet were all the results are shown but it takes me a lot to update all of them so I was wondering if it would be possible to make a form in order to update them more easily.

In the form I will enter the driver name and the number o points he won on a race. The championship has 4 races each month so yea, my question is if you guys know a way to update an existing data (stored in a spreadsheet) using a form. Lets say that in the first race, the driver 'X' won 10 points. I will insert this data in a form and then call it from the spreadsheet to show it up, that's right. The problem comes when I want to update the second race results and so on. If the driver 'X' gets on the second race 12 points, is there a way to update the previous 10 points of that driver and put 22 points instead? Or can I add the second race result to the first one automatically? I mean, if I insert on the form the second race results can it look for the driver 'X' entry and add this points to the ones that it previously had. Dunno if it's possible or not.

Maybe I can do it in another way. Any help will be much appreciated! Thanks.

jupcan
  • 436
  • 3
  • 7
  • 17

3 Answers3

5

I believe you can found everything you want here.
It's a form url, when you answer this form you'll have the url of the spreadsheet where the data are stored. One of the information stored is the url to modify your response, if you follow the link it will open the form again and update the spreadsheet in consequence. the code to do this trick is in the second sheet of the spreadsheet.
It's a google apps script code that need to be associated within the form and triggered with an onFormSubmit trigger.

Harold
  • 3,297
  • 1
  • 18
  • 26
  • Thank you Harold for your answer! That's why I'm and I'm not looking forward to, I mean, it's not exactly what I'm looking for cause I can edit the results but I have to update all the results manually (adding the points of each driver for each race of the champ). – jupcan Feb 15 '14 at 00:31
  • Hi Harold. I think your solution is almost what I need to associate a record in a sheet to a form. But when I'm in the sheet and click on the link to the form, edit something, and then press submit, your solution creates a new record. What I'd prefer is a solution where each record has a link to a form and editing the form updates the record. Is that possible? – Christopher Werby Jul 11 '16 at 01:40
  • @christopher Hi, I belive it was exactly what it was supposed to do, but somehow it's not working anymore I'll fix that next week I'm traveling this week – Harold Jul 11 '16 at 05:51
  • Two and a half years ago, you wrote an answer on Stack Overflow, and now I'm asking you to provide technical support on it! Thanks, Harold! I really do appreciate it. – Christopher Werby Jul 11 '16 at 17:00
  • @ChristopherWerby Done I've updated the script it should be working correctly again – Harold Jul 20 '16 at 13:29
  • @Harold Marvelous! I checked it out and it works just as it should. (There's a weird delay after updating the form before the data appears in the spreadsheet, but that's Google's issue. I mention it only because I thought it hadn't worked and then, after ten seconds or so, the field updated.) Now I have to figure out how you did all that magic! Thank you very much! – Christopher Werby Jul 21 '16 at 19:41
5

Maybe I missed something in your question but I don't really understand Harold's answer... Here is a code that does strictly what you asked for, it counts the total cumulative value of 4 numbers entered in a form and shows it on a Spreadsheet.

I called the 4 questions "race number 1", "race number 2" ... and the result comes on row 2 so you can setup headers.

I striped out any non numeric character so you can type responses more freely, only numbers will be retained.

form here and SS here (raw results in sheet1 and count in Sheet2)

script goes in spreadsheet and is triggered by an onFormSubmit trigger.

function onFormSubmit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var responses = []
  responses[0] = Number(e.namedValues['race number 1'].toString().replace(/\D/g,''));
  responses[1] = Number(e.namedValues['race number 2'].toString().replace(/\D/g,''));
  responses[2] = Number(e.namedValues['race number 3'].toString().replace(/\D/g,''));
  responses[3] = Number(e.namedValues['race number 4'].toString().replace(/\D/g,''));
  var totals = sh.getRange(2,1,1,responses.length).getValues();
  for(var n in responses){
    totals[0][n]+=responses[n];
  }
  sh.getRange(2,1,1,responses.length).setValues(totals);
}

edit : I changed the code to allow you to change easily the number of responses... range will update automatically.

EDIT 2 : a version that accepts empty responses using an "if" condition on result:

function onFormSubmit(e) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var responses = []
  responses[0] = Number((e.namedValues['race number 1']==null ? 0 :e.namedValues['race number 1']).toString().replace(/\D/g,''));
  responses[1] = Number((e.namedValues['race number 2']==null ? 0 :e.namedValues['race number 2']).toString().replace(/\D/g,''));
  responses[2] = Number((e.namedValues['race number 3']==null ? 0 :e.namedValues['race number 3']).toString().replace(/\D/g,''));
  responses[3] = Number((e.namedValues['race number 4']==null ? 0 :e.namedValues['race number 4']).toString().replace(/\D/g,''));
  var totals = sh.getRange(2,1,1,responses.length).getValues();
  for(var n in responses){
    totals[0][n]+=responses[n];
  }
  sh.getRange(2,1,1,responses.length).setValues(totals);
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thank you very much for your help! Much appreciated! It's what I was looking for but still not exactly what I'd really like to have. The script you gave me adds up the results of the four races but in order to do so I need to insert the 4 results at once. I mean, if I could just insert one result each time it will be much more useful cause I have to update the results weekly (4 races a month). – jupcan Feb 14 '14 at 22:48
  • feel free to describe with more details so that I understand better :-) – Serge insas Feb 14 '14 at 22:50
  • And one more thing. If I have 13 drivers, could be a way to select the driver result I want to insert in the form or I will need to insert 4 form questions for each driver? That's why I was saying if it was possible to edit the content of a spreadsheet using a form cause if possible I will insert the race1 result for drivers 1, 2 and 3 in the first week and then their results for race2 but just updating the previous one or adding them up. Couldn't edit the first answer on time, sorry for that. Thank you very much again Serge! :D – jupcan Feb 14 '14 at 22:56
  • For sure you can... so there would be 5 questions? Question 1 being the name of the driver? How are they shown on the SS? – Serge insas Feb 14 '14 at 23:01
  • Note that no answer is mandatory so you can leave some questions blank...I guess it wouldn't cause an error... to be tested... – Serge insas Feb 14 '14 at 23:07
  • Yea. The question number 1 will be the driver name and then the others the results in each of the 4 races a month. So in the first week I will insert the name of the driver and the points he won on the first race, then on the second week I will just insert the points he won on the second race (and the name again if needed) and so on... So the SS will add all the driver points (1, 2, 3 and 4 week). And the same with the other 12 drivers. It's somehing difficult and I'm wondering if it could be done or not using Google Docs. :/ – jupcan Feb 14 '14 at 23:12
  • Have tried just inserting race1 results and leaving the ohers blank but it didn't work for me Serge. The SS was not adding the points. – jupcan Feb 14 '14 at 23:14
  • Not so difficult I guess... could you share a spreadsheet with data shown the way you want? I'll add the code to fit your sheet layout. As for your last comment, I'll update the code to handle empty responses :-) – Serge insas Feb 14 '14 at 23:15
  • Sure. Here you go! - https://docs.google.com/spreadsheet/ccc?key=0Akxb4pnt7jeOdERxVlNwQllWaGpfZzQwOVhScXJnOVE&usp=drive_web#gid=0 - Thanks for handling empty responses too. :) – jupcan Feb 14 '14 at 23:20
0

It may be too late now. I believe we need a few things (I have not tried it)

  1. A unique key to map each submitted response, such as User's ID or email.
  2. Two Google Forms:
    a. To request the unique key
    b. To retrieve relevant data with that unique key
yoonghm
  • 4,198
  • 1
  • 32
  • 48