Custom Web Form to Google Sheets

Sometimes the options given in Google Forms just won’t quite work for what you want to do. Maybe you want a particular look, or an interaction, or whatever that Google Forms just won’t do. Luckily, it’s not too hard to make a custom form that can do whatever you want and still has the ability to write the submitted data to a Google Spreadsheet and the form HTML is still served by Google. The following steps should get you up and running and comments in the scripts should provide additional details. Make a new spreadsheet in Google Sheets. Go to Tools>Script Editor Select all that stuff and replace it with the content below. Replace the string of ****** with the ID of your spreadsheet. Then save it. If you get any permissions prompts approve them. Make a new HTML page (File>New>HTML File) and name it index.html Select all and replace it with this.1 Save it. To make sure things work, let’s publish it (Publish>Deploy as Web App). Now go to that URL and submit something and see if it goes to the spreadsheet. If so, great. Now you can start customizing the form to reflect your needs. This form should now write to a spreadsheet like this. Do keep in mind that each form field you want to write to […]

Pre-Filling Forms via URL

I have to figure out a rather unpleasant and boring thing. I am, however, learning some fairly odd and interesting tricks as a result. This is one that might be useful to someone. Google Forms You can pre-fill Google form entries with a URL. That might be useful if you had 720 students in groups of 6 reviewing one another but didn’t want to build a form with 720 student names or build a 120 forms with 6 student names. I don’t think I’m going to end up using this for this purpose1 but maybe it’ll prove useful to someone else and it’s dead simple. Step one – Build your form. Step two – Go to Responses in the Form Editor view and select “Get pre-filled URL”. You then fill out the form the way you want and it creates the URL. In this case, I’m filling out a multiple choice question and a free form text entry. https://docs.google.com/forms/d/1P5_6vTv53MEKCEjd87xecI483goNqDg1-nPlFH84Mz0/viewform?entry.1615031756=Bob+Smith&entry.1012634392=I,+for+one,+have+always+admired+the+number+two. Now, you might wonder what would happen if in the URL you set a multiple choice answer to something not available as an option- like ‘Freddy Kruger’ for the first field in the form. I wondered that. It just comes up blank in the spreadsheet.2 Sadly, as I mucked around I couldn’t come up with a decent way to hide the […]

Google Forms to Exhibit Example (POC)

So, I’ve managed to create two quick websites for work that are driven by Google’s new form option for getting data into spreadsheets. I’ve put a quick example of a log here. Feel free to enter data etc. It’s up there to play around with and hopefully is simple enough to help people figure out how to do it. One thing I don’t like about the form option. I don’t like that changes I make to the submission form alter my spreadsheet. I might want the form to read “Your name here” while my spreadsheet says {name:text}. I don’t believe there’s any way to do that and it would be much nicer when using this with Exhibit. Instead I have to add another sheet and I use a formula to reference the data in. It’s just =sheet1!A2 in case anyone needs it. Then if I get my mouse in just the right place it turns into cross hairs and I can drag that formula dynamically so that it pastes as =sheet1!A3 and A4 etc. then I can drag it across to create =sheet1B2 etc. That is much better than typing all that in. In a perfect world I’d also be able to apply some css to it but that’s getting a little picky. So the key steps. create spreadsheet and […]