Hi. I have a solution to the problem you described. You need to make two small changes: 1) Add another checkbox called "nobody". 2) Make the question a "required question". I tested this and it works. The problem happens because the form has no new text to overwrite the existing entry. Now the person's name will be replaced by "nobody". The required question part is needed because this prevents submitting the form without selecting any checkbox. Thanks for the great video. Another idea you could consider making a video about could be a dynamic droplist with arrayformula integration. First there is a droplist that says, "Select class". When you do that, the items inside the next droplist update to show the student list. Then you select a student from this class. Then it populates two columns. One is absent and the other late. This way you could create (and print) a report for some students with dates they were late and absent. I absolutely love the droplist feature (under data validation menu) in Google forms. It is so much better than excel.
+Troy F. Hi, Thanks! That's clever. I've gotten into using arrayformulas a lot lately, too. I will definitely revisit my spreadsheet and try your suggestion.
Hello Richard, I work for a High School District Transportation Department and I track weekly overtime hours for our bus drivers to determine who should be awarded trips first. I would like to create a google form where the drivers can input their total overtime hours and I can manage that information in Sheets with each week being input to the respective column for the week ending date and each row where the drivers names appear. Presently I have the drivers turn in their weekly hours I manually input the hours and sort the total hours column which reorganizes the drivers in the list from the least to most hours. It is very simple doing it manually but when I started researching how to create a form linked to my sheet so the drivers could enter their hours, I realized how complex this tack actually is and I don't really know where to begin. I am including a link to the sheet that I am presently using manually in hopes that you or someone else might be able to direct me to an example that will help me. I am not versed at all in using complex formulas on spreadsheets but I would love to learn. Any help would be greatly appreciated! Here is the link... docs.google.com/spreadsheets/d/1bDD5gZIT1w7kshmQzu_OD5RR8VrqDm5MmcqCvnny9QQ/edit#gid=336847276 Thanks!
Could the intermittent behavior of the URL fetching function be because the Doc item is sometimes not yet created before the Sheet script tries to fetch its address? If so, either adding a pause timer into the script and/or doing a FIND test on the contents of the retrieved string to see if you need to try again could be solutions.
Was there not a way to do checkboxes under each student to do present, absent, late for a single entry for each? I know you could do dropdowns or multiple choice. But I wonder if there's a horizontal option for this. I've tried the matrix, but the spreadsheet side sucks
Hi, trying to help about the issue on responses edition: Have you marked the option "Allow responders to edit responses after submitting" when creating the form? Hope it helps.
Richard, this is a great tutorial. Can I ask why you didn't go with the grid option for your Absent and Late options, with names of students listed on left side?
The only drawback I see is that the raw form responses force student names into columns, time stamp into rows, and grid options into cells. I'm building something similar but have 30+ classrooms to track lunch menu choices and it makes the Sheet very difficult to navigate. I'm trying to figure out if there's a way to force Google Forms to consolidate responses to a day, so that it doesn't create extra rows for the time stamp. Any ideas? docs.google.com/spreadsheets/d/1DxbyIhCSi9NGdhFWGK88AV29uYoZZ9tDcXDHuYGx2q0/edit?usp=sharing
It seems difficult to me is there any written procedure for the same, so that I can follow these steps to create my own form like the video shown. Please have a support.
Hello, i wanted to tell you you've made a great job! Also i wanted to ask you if you could help me, i am an Italian student and i've just created a docs form for my school, there are some courses and every student must sign up for a different course. My problem is that each course is done into a classroom that can content a maximal number of students, so i need to let them sign up until we get to that number and then the course must disappear from the list. I guess it would pretty hard to do it manually, i mean: i cannot wait for the student to reach the max number of participants and then just remove the course from the available list because in case someone has already voted he cannot do it again and so i would have to move them to a different course and it would be so bumbling you know? Hope you have any idea about how to help me and sorry for my bad english!
Hi, great video. I have been looking for this for a while. I am having problems with the script: the last line: sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); is giving me this error: The coordinates or dimensions of the range are invalid. (line 21, file "Code") Any idea about how to solve it? Thanks a lot.
Hi Richard, thanks a lot for your response. My sheet has data until the column "P", so I set it to start writing the data to the column "Q" wich is the 17. The data begin at the row 2.
This is the script. It looks fine. I have even given authorization for the script to run. Any idea? function assignEditUrls() { var form = FormApp.openById('I wrote here the form ID, but I removed it or this post'); //enter form ID here var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Evento 1'); //Change the sheet name as appropriate var data = sheet.getDataRange().getValues(); var urlCol = 17; // column number where URL's should be populated; A = 1, B = 2 etc var responses = form.getResponses(); var timestamps = [], urls = [], resultUrls = []; for (var i = 0; i < responses.length; i++) { timestamps.push(responses[i].getTimestamp().setMilliseconds(0)); urls.push(responses[i].getEditResponseUrl()); } for (var j = 1; j < data.length; j++) { resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']); } sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); }
Hi +Richard Anderson. I want to do something similar with 40 teams in a bible tournament. I have created a single google sheet where I have all my teams together next to each other exactly as your sheet. Each team is supposed to answer 90 questions worth 1 up to 5 points each. All the possible points are already entered in the main sheet. I just want to create a different form for each team so that their score keeper enters the points they got for each question. Let's say question number 1 is worth 2 points. The score keeper of that team will enter 1 if that's what they got. At the end for the form, all points should be sent to a column next to the possible points column. I have already formatted the main sheet with formulas to pull all winner, etc. So I just want to send all the 90 scores in each form to a specific place on my main sheet. I hope it makes sense. I haven't been able to find anything similar to what I want to do and finally this seems to be pretty close. I hope you can help. thanks!
Seeing as though you have 40 teams and each team will have it's own form I would recommend using the =ImportRange function on a master Google sheet to bring in a selection of data from all of the other sheets. You should probably award the points on all of the individual sheets to include that in your import. Better yet, combine =IMPORTRANGE with the =QUERY function and you can order the data exactly the way you want. Unfortunately, I don't have a lot of time right now to help, but this should put you on the right track.
Hello I am getting this feedback when i try the formula =ARRAYFORMULA(COUNT(IFERROR(FIND(B3,'Form Responses 1'!$D$2:D)))) feedback: Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings
Also, were you able to sort out getting a predefined list? That's a matter of building some form branching logic into the actual form. In other words, have the form go to a specific page based on a selection in the form.
I also referenced the name of the student. feedback: Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings
Thank you so much Richard Anderson for helping me finally sort this out for my use case. This links to a public copy of my spreadsheet docs.google.com/spreadsheets/d/14zebSaV62oB7XJZheadl1Rntkc-ZjOQlDSSsN7vxygE/edit?usp=sharing You might also look to Ben Liebrand's advice in this thread. productforums.google.com/forum/#!topic/docs/1-7p_dzR3k8
Hi. I have a solution to the problem you described. You need to make two small changes: 1) Add another checkbox called "nobody". 2) Make the question a "required question". I tested this and it works.
The problem happens because the form has no new text to overwrite the existing entry. Now the person's name will be replaced by "nobody". The required question part is needed because this prevents submitting the form without selecting any checkbox.
Thanks for the great video. Another idea you could consider making a video about could be a dynamic droplist with arrayformula integration. First there is a droplist that says, "Select class". When you do that, the items inside the next droplist update to show the student list. Then you select a student from this class. Then it populates two columns. One is absent and the other late. This way you could create (and print) a report for some students with dates they were late and absent. I absolutely love the droplist feature (under data validation menu) in Google forms. It is so much better than excel.
+Troy F. Hi, Thanks! That's clever. I've gotten into using arrayformulas a lot lately, too. I will definitely revisit my spreadsheet and try your suggestion.
Hello Richard,
I work for a High School District Transportation Department and I track weekly overtime hours for our bus drivers to determine who should be awarded trips first.
I would like to create a google form where the drivers can input their total overtime hours and I can manage that information in Sheets with each week being input to the respective column for the week ending date and each row where the drivers names appear. Presently I have the drivers turn in their weekly hours I manually input the hours and sort the total hours column which reorganizes the drivers in the list from the least to most hours.
It is very simple doing it manually but when I started researching how to create a form linked to my sheet so the drivers could enter their hours, I realized how complex this tack actually is and I don't really know where to begin.
I am including a link to the sheet that I am presently using manually in hopes that you or someone else might be able to direct me to an example that will help me. I am not versed at all in using complex formulas on spreadsheets but I would love to learn. Any help would be greatly appreciated!
Here is the link...
docs.google.com/spreadsheets/d/1bDD5gZIT1w7kshmQzu_OD5RR8VrqDm5MmcqCvnny9QQ/edit#gid=336847276
Thanks!
Thanks for this vid. Any idea on how to add two numbers on a google form?
+Thomas Jones Hi Thomas, could you explain a little bit more about what you mean?
Hi! Great stuff! I'm am trying to create a grocery list from responses on google forms. Do you think I can use an array formula to do this?
Could the intermittent behavior of the URL fetching function be because the Doc item is sometimes not yet created before the Sheet script tries to fetch its address? If so, either adding a pause timer into the script and/or doing a FIND test on the contents of the retrieved string to see if you need to try again could be solutions.
Was there not a way to do checkboxes under each student to do present, absent, late for a single entry for each? I know you could do dropdowns or multiple choice. But I wonder if there's a horizontal option for this. I've tried the matrix, but the spreadsheet side sucks
Hi Jason, to my knowledge there isn't a way to do that. I haven't revisited this in a while so maybe there is a better way now.
Hi, trying to help about the issue on responses edition: Have you marked the option "Allow responders to edit responses after submitting" when creating the form? Hope it helps.
+Edgar Lagos Yep. I appreciate the thought. I haven't revisited this issue in a while, maybe it will just work now.
:O
Richard, this is a great tutorial. Can I ask why you didn't go with the grid option for your Absent and Late options, with names of students listed on left side?
Good idea! To be honest it hadn't crossed my mine. I suppose this could be adapted to do that as well.
The only drawback I see is that the raw form responses force student names into columns, time stamp into rows, and grid options into cells. I'm building something similar but have 30+ classrooms to track lunch menu choices and it makes the Sheet very difficult to navigate. I'm trying to figure out if there's a way to force Google Forms to consolidate responses to a day, so that it doesn't create extra rows for the time stamp. Any ideas? docs.google.com/spreadsheets/d/1DxbyIhCSi9NGdhFWGK88AV29uYoZZ9tDcXDHuYGx2q0/edit?usp=sharing
It seems difficult to me is there any written procedure for the same, so that I can follow these steps to create my own form like the video shown. Please have a support.
Cai i see it ?
hello I want to find out the total attendance in 1 month for a particular person. can you help me plz
looking for the same
@@akbarali3621 this the advance one, may you could asked him ua-cam.com/video/4odI4qtZiH0/v-deo.html
Hello, i wanted to tell you you've made a great job! Also i wanted to ask you if you could help me, i am an Italian student and i've just created a docs form for my school, there are some courses and every student must sign up for a different course. My problem is that each course is done into a classroom that can content a maximal number of students, so i need to let them sign up until we get to that number and then the course must disappear from the list. I guess it would pretty hard to do it manually, i mean: i cannot wait for the student to reach the max number of participants and then just remove the course from the available list because in case someone has already voted he cannot do it again and so i would have to move them to a different course and it would be so bumbling you know? Hope you have any idea about how to help me and sorry for my bad english!
+Abel Bahmani Hi. There is an add-on for the old Google Forms called Form Limiter. This will do exactly what you are looking for.
Hi, great video. I have been looking for this for a while. I am having problems with the script: the last line: sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); is giving me this error: The coordinates or dimensions of the range are invalid. (line 21, file "Code")
Any idea about how to solve it?
Thanks a lot.
How many columns in your sheet contain data? and what row does your data begin?
Hi Richard, thanks a lot for your response. My sheet has data until the column "P", so I set it to start writing the data to the column "Q" wich is the 17. The data begin at the row 2.
This is the script. It looks fine. I have even given authorization for the script to run. Any idea?
function assignEditUrls() {
var form = FormApp.openById('I wrote here the form ID, but I removed it or this post');
//enter form ID here
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Evento 1');
//Change the sheet name as appropriate
var data = sheet.getDataRange().getValues();
var urlCol = 17; // column number where URL's should be populated; A = 1, B = 2 etc
var responses = form.getResponses();
var timestamps = [], urls = [], resultUrls = [];
for (var i = 0; i < responses.length; i++) {
timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
urls.push(responses[i].getEditResponseUrl());
}
for (var j = 1; j < data.length; j++) {
resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
}
sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
}
Are you using the Form ID while it's in edit mode?
I just copied and pasted your above script into a new example and it seems to work fine. Is your Form Response sheet called "Evento 1"?
Hi +Richard Anderson. I want to do something similar with 40 teams in a bible tournament. I have created a single google sheet where I have all my teams together next to each other exactly as your sheet. Each team is supposed to answer 90 questions worth 1 up to 5 points each. All the possible points are already entered in the main sheet. I just want to create a different form for each team so that their score keeper enters the points they got for each question. Let's say question number 1 is worth 2 points. The score keeper of that team will enter 1 if that's what they got. At the end for the form, all points should be sent to a column next to the possible points column. I have already formatted the main sheet with formulas to pull all winner, etc. So I just want to send all the 90 scores in each form to a specific place on my main sheet. I hope it makes sense. I haven't been able to find anything similar to what I want to do and finally this seems to be pretty close. I hope you can help. thanks!
Seeing as though you have 40 teams and each team will have it's own form I would recommend using the =ImportRange function on a master Google sheet to bring in a selection of data from all of the other sheets. You should probably award the points on all of the individual sheets to include that in your import. Better yet, combine =IMPORTRANGE with the =QUERY function and you can order the data exactly the way you want. Unfortunately, I don't have a lot of time right now to help, but this should put you on the right track.
thanks a lot
Hello I am getting this feedback when i try the formula
=ARRAYFORMULA(COUNT(IFERROR(FIND(B3,'Form Responses 1'!$D$2:D))))
feedback:
Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings
What do you have in cell B3? In my example I reference the name of the student A3.
Also, were you able to sort out getting a predefined list? That's a matter of building some form branching logic into the actual form. In other words, have the form go to a specific page based on a selection in the form.
Yes i got this sorted out
I also referenced the name of the student.
feedback:
Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings
If a user answers the first question with the number Two and the next question with a Three...can the form then multiply 2 x 3 and show 6?
+Thomas Jones The form can't do that, but the spreadsheet where the form responses go can do that pretty easily.
Hi , the code ( script) to edit a submitted response seems not to work for me.
Hi, Did you confirm you're using the Form ID for your form and not the one in the sample I shared?
Yes i am using mine
any help here?
Can you share a link to your sheet?
docs.google.com/spreadsheets/d/1VD7WWo2UBOOVwMLj9NVRhtryOa_ff7QUA3UHJkHMlHs/edit?usp=sharing
I think if you had hit refresh, Amber's name would have been removed...
yes
Thank you so much Richard Anderson for helping me finally sort this out for my use case.
This links to a public copy of my spreadsheet docs.google.com/spreadsheets/d/14zebSaV62oB7XJZheadl1Rntkc-ZjOQlDSSsN7vxygE/edit?usp=sharing
You might also look to Ben Liebrand's advice in this thread. productforums.google.com/forum/#!topic/docs/1-7p_dzR3k8
I don't know what Google's form is I am not interested.
Though you have the time to leave a comment like that ? Have a great day.