Using Google Forms + Sheets for Quality Control

51 views
Skip to first unread message

Richard Ferreira

unread,
Aug 14, 2019, 8:51:14 AM8/14/19
to Google Apps Script Community
Hello everyone,


I'm trying add features to a Google Forms and Spreadsheet currently in use for Quality Control in a manufacturing environment. Not having much luck using Google Apps Script in getting some features up and running (mostly due to lack of experience with Apps Script), so thought I'd reach out here. The features are as follows:

  • Automatically populate the Date & Time the Form is opened into the Spreadsheet, much like how the submission time currently does by default;
  • Populate an Item Number in the Form when it's opened, based on the latest Item Number in the Spreadsheet;
  • On Form submission, return a page with some of the details the user just submitted, for documenting purposes.

I've fiddled around with Scripts both in the Forms and the Spreadsheets, but the issue is that scripts just won't run when the technicians open the Form in View Only, and they don't even have access to the Spreadsheet, let alone Edit access.

Wrapping my head around Google Apps Script has also been slow, since my background is in Mech Eng.

Any help would be greatly appreciated!

Dimu Designs

unread,
Aug 14, 2019, 9:32:53 AM8/14/19
to Google Apps Script Community
Your first two items are non-starters because of the following restriction (quoted from the documentation):
This event does not occur when a user opens a form to respond, but rather when an editor opens the form to modify it.
 
That means a google form will not fire an onOpen event when opened by an end user.

You can try creating a custom form via a GAS Web App, but you'll need to know HTML, CSS and Javascript to pull it all together.

Martin Molloy

unread,
Aug 14, 2019, 9:58:06 AM8/14/19
to google-apps-sc...@googlegroups.com
If you use a pre-fillled url to populate the form you could pass a time and an item number. So you set up a spreadsheet which reads your original sheet and gives access to your users. In your new sheet you can calculate the new link which would then populate your form. You can hide the hyperlink in a generic click here message or something similar.

You can't hide the fields you are automatically populating so your users could overwrite that data before they submit the form.

There are a few existing add-ons which will return data from a submitted form. FormMule and Autocrat are both free and very good.

Martin

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/11332edd-dcd6-4a1f-bdd2-01b269754ba8%40googlegroups.com.

Richard Ferreira

unread,
Aug 14, 2019, 10:01:10 AM8/14/19
to Google Apps Script Community
Ah, I didn't want to believe it, but it makes sense. Thanks!

The custom form, would it be a work around for the first 2 points or for the third one? 

Richard Ferreira

unread,
Aug 14, 2019, 10:09:10 AM8/14/19
to Google Apps Script Community
Oh, I wasn't aware a pre-filled URL could be used to populate forms, I'll definitely look into that. Thanks!

Though I would prefer if they couldn't edit the populated fields, but if it can't be helped then it would already be more than we currently have.

Ah sweet, those add-ons went over my head completely! Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Fernando Falcao

unread,
Aug 14, 2019, 1:57:17 PM8/14/19
to google-apps-sc...@googlegroups.com
Hello Richard,

In the form that you can connect clicking the link https://forms.gle/oPesnk1Whzq4HwFB6 you will see a list box item that is updated with current time at each minute. That's an alternative that I thought that may help you. Every time your final user open this form, it will bring the current time with a gap of one minute difference. It this gap won't be an issue for you, it may be helpful.

Here is the code that runs on a google script project:

function UpdateCurrentTime()
{

  var frm = FormApp.getActiveForm()  //Get the current Google form.
 
var idList = frm.getItems()[0].getId(); // Get the id of the dropdown list object

var frmCurrentTime = frm.getItemById(idList).asListItem() //get the field.

var vetCurrentTime = []; //create an array to receive the current time.

vetCurrentTime.push(new Date()); //get the current time and assign to the array.

frmCurrentTime.setChoiceValues(vetCurrentTime) //update the field.

}

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4264fee9-a820-4694-984d-64c19a3edcf4%40googlegroups.com.

Richard Ferreira

unread,
Aug 15, 2019, 2:37:12 AM8/15/19
to Google Apps Script Community
Ah sweet, that's exactly what I need!

I've tried adding it to my own form, but it simply doesn't run when I preview the Form, only when I click the Run button on the script editor. It's driving me mad, since that's what's keeping me from running any scripts on view only.

Any idea on what I might be doing differently?

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Fernando Falcao

unread,
Aug 15, 2019, 9:33:01 AM8/15/19
to google-apps-sc...@googlegroups.com
Hi Richard!

Did you add a trigger in your project in order to run the function at each minute?

Once you do this association to the trigger you will have the function running on a periodicity of minute.
Cheers,
Fernando

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/376c4a58-b216-4eaf-b191-6fc09e793ba3%40googlegroups.com.

Richard Ferreira

unread,
Aug 15, 2019, 9:42:24 AM8/15/19
to Google Apps Script Community
Hi Fernando!

Oh man, thanks so much! I thought I did but I actually hadn't. Working perfectly now!

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages