Google-sheets – Add unique QR code field to a spreadsheet based on a form in google docs using Script Trigger

google sheetsgoogle-apps-scriptgoogle-forms

At the end of each row filled in, using a custom Google Form, I'm adding three more columns:

Date of creation
Unique ID
QR code (it is a list of computers)

Unique ID and QR code are unique. QR code is generated from the values of all the cells in the form.

How can I create a script trigger to generate the three cells: Date, Unique ID and QR code onsubmitform?

Here is the code for the cell, for how I simply copy the formulas to generate the QR codes:

Date: =Today()
Unique ID: =CONCATENATE("ROTA-",LEFT("0000",4-LEN(ROW(A2))),ROW(A2)-1)
QR code: =if(isblank(B2), "", image("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl=Type:"&B2&", "&"Make:"&C2&", "&"HDD:"&D2&", "&"RAM:"&E2&", "&"Drive:"&F2&", "&"Keyboard:"&G2&", "&"Monitor:"&H2&",

The last one is pretty long and each value needs to be adjusted to the next row values.
Basically I need to do something like this but more difficult: reference question

Best Answer

Instead of using a script, consider to add the responses to another sheet using formulas and there add the three columns.

It's worth to say that Today() column will be updated when the recalculation occurs.

I.E. Adding a column with the formula =Today() to the responses of a form add the following formula to A1 in a new sheet:

=ArrayFormula(
{
filter(Sheet1!A:B,Sheet1!A:A<>""),
transpose(
split(
rept(
Today()&char(9),
countif(Sheet1!A:A,"<>")
),
char(9)
)
)
}
)

Related Solutions

Google Sheets – How to Add an Autoincrement Field Based on Google Form

You can do this by adding a script trigger.

Supposing your current Form has two columns Timestamp and the answer to one question. So you currently have columns A and B populating with data. Lets assume you want column C to have you auto increment number.

You need to first go to Tools > Script Editor

In the Script Editor window enter the following script:

function onFormSubmit(e) {

var sheet = SpreadsheetApp.getActiveSheet();
var row =  SpreadsheetApp.getActiveSheet().getLastRow();

sheet.getRange(row,3).setValue(row);


}

Save the script and then go to the Triggers menu and select Current script's triggers

Populate the drop downs as follows:

Google Script Triggers

Click Save

Then Save and close the Google App Script window.

Now when your form is submitted it will populate the row number in column C along with the data that has been submitted via your form.

In you want to change the column that the row number gets saved to you need to change this line of the script:

sheet.getRange(row,3).setValue(row);

and change the value 3 to the corresponding column index number.

Google-sheets – How to implement a script that creates a unique ID for a Google form submission

The problem is in the first line of the script. Your form ID is the special ID that identifies the form. You can see it in the address of the form (it's the bit in bold)

docs.google.com/forms/d/17rxZ5vzy0OcxibXV6aRpso7Xm_Vs0FpxMLgbchjO998/viewform.

You accidentally used the form name instead.

That being said, I should have just used the Spreadsheet.getFormUrl() in the original answer. I'll change that now, then you won't have to worry about the ID at all.

Best Answer

Related Solutions

Google Sheets – How to Add an Autoincrement Field Based on Google Form

Google-sheets – How to implement a script that creates a unique ID for a Google form submission

Related Topic