Google Forms – Change File Upload Name to Match Form Value

google sheetsgoogle-apps-scriptgoogle-forms

I have a Google Form for contact information. The form asks for Given Name (col A), Family Name (col B) and a File Upload restricted to images only (col D). I have a formula which displays the Full Name [Given Name] [Family Name] in col C.

When the form uploads the image files it keeps their original file name, however, I would like to change each uploaded file name to match the Full Name value in col C.

Couldn't find a way to do it and I suspect it would require a script?

Best Answer

You are right, renaming an uploaded file could be done by using a script and it should be used if you want that file name changes on form submission.

You will require to use the Driver Service or the Drive Advanced Service.
You could use an onFormSubmit trigger to do this when the response is submitted but you could do the file name change at other time.
NOTE: There are two onFormSubmit triggers, one for projects bounded to Forms and other projects bounded for Spreadsheets.

Reference

https://developers.google.com/apps-script

Overview

The built-in response summary of Google Forms is not customizable, but you could set your form to send responses to a spreadsheet and use it to do assist you to count the responses by organizational units. You could create a "master" spreadsheet to use as template to do the follow up of each Google Form.

Example of the "master" spreadsheet setup

Create a spreadsheet
Add an additional sheet
Rename the sheets as follow:
- Responses
- Roster

Responses

This sheet will be used as the destination for the responses of one form.

Roster

Use this sheet to hold the list of email addresses and the OU that each of them belongs to. This sheet also will be used to check if each email address sent at least one response. Columns:

Column A: email addresses
Column B: organizational unit
Column C: response

In the response column use the following formula

=ISERROR(MATCH(A2,Responses!B:B,0))<>NA())

Report

Insert a pivot table using the range of data in the Roster sheet.
1. Click the Data menu, then Pivot Table.
2. Write Roster!A:C, then click in the OK button

A third sheet will be created showing a blank pivot table and its settings panel. In the settings panel set the following:

Rows: Add the Organizational Unit field.
Columns: Add the Response field.
Values: Add the email address field. Summarize by COUNTA.

Rename the sheet as Report.

Next steps

Now the spreadsheet is ready as a "master" spreadsheet. You could use it to create a copy of for each of form that you require to do a follow up by organizational unit. After that, set Responses sheet as the destination of the responses of the corresponding form.

Google-apps-script – How to change position of column form responding sheet after add form item

It is not possible for a script to rearrange columns linked to a form. The sheets and columns linked to a form are special, and this special nature is not something Apps Script can see. (Explanation below).

Alternative approach

Create another sheet, Real Form Data, in which the first row is filled with formulas such as

={'Form Responses 1'!A:A}, ={'Form Responses 1'!C:C}, ={'Form Responses 1'!B:B}

in whatever order you wish. These formulas can be manipulated by a script in any way you want, with setFormula(s) methods. You can hide the original sheet with form data, and work only with this new one, which has the desired order of columns.

Explanation

There is no script method for "moving" the way we move rows and columns in the interface. Their surrogates, described in Moving a column in Google Spreadsheet, are

Insert a column, copy values there, delete the original.
Take values of the entire sheet, rearrange them script-side, then write back.

Method 1 doesn't work for form data because the new column wouldn't be linked to the form (and the original column can't be deleted).

Method 2 doesn't work because changing the values in columns doesn't affect their invisible link to the form: it will still send data in the same way as it did.