I am trying (and not succeeding at the moment) to populate a Google Form from a Google Spreadsheet using the items that I have picked up from this website and this extremely useful answer provided by Mogsdad here.
Ideally, I'm looking for:
The Logger.log (URL) logs URL's for all the data stored in the spreadsheet, is it possible to just log the last entry and use this to generate the URL?
Is it then possible for the pre-filled URL to auto submit once populated with the data? I have found this useful article here which suggests that this can be done?
The data that is stored in the Google Spreadsheet is data captured from another Google Form. This is due to the need of using Excel (lack of Internet connectivity) with a concatenate formula to merge all cells with data into one. This is then submitted on the other Google Form which has this script to split the data out by column ready to answer the questions with. Will this impact the trigger needed to auto submit when a submission is made?
May I add that I have a rather limited understanding on this so please go easy if this seems rather easy to do!
is it possible to just log the last entry and use this to generate the URL?
I'm not sure I follow you here - in that other answer, the Logger.log() statement was just to demonstrate that you could generate the correct URL, if you wanted to distribute it. Instead of logging it, you'd just use the content of the variable url.
But let's move on, because I think this is a little off your path.
Is it then possible for the pre-filled URL to auto submit once populated with the data?
There's a better starting point. Can we programmatically submit information from a spreadsheet into a google form? Sure! See Use App Scripts to open form and make a selection. It's a more reliable way to do the job than what you see in the "URL Tricks" post.
In the case of "auto submit", or simulating a form submission, you don't need to worry about the pre-filled URL at all. That's a shortcut for pesky humans. What you want is to put together the payload for a POST request, instead, and have a computer bypass the form UI altogether.
Something about Excel... Will this impact the trigger needed to auto submit when a submission is made?
(Sounds like...) You are using Form1 to get data into Spreadsheet1, then expecting to react to the (human?) submission of Form1 by having the machine submit Form2 after breaking apart the data from Form1.
Yeah, you'll need to be careful that the column split is done before you try to read the information to submit Form2.
I suggest that you would be best served with a form-submission trigger function for Spreadsheet1 that splits the string received from Form1 then immediately sends the POST to Form2. I'd then record the fact that this action has occurred, using the technique from Spreadsheet Email Trigger.