Add custom Data to Google Sheet once Form is filled

Question

I am using a Goofle form that receives applications from users, once the form is submitted I am using Google form submission trigger to notify. It has some questions like (facebook profile link)

But while sending a notification I don't want those links to be appeared just the same way user enters, instead something like Facebook, twitter (text as hyperlinked.) should appear

I already know about onFormsubmit() function, but not sure how to use, help me out please.

For example:

Cell A2 has user submitted facebook profile link using the form.

I want Column B2 to automatically generated as =hyperlink(A2, "Facebook")

The same thing should happen like A3 to B3 whenever user submits a form.

A formula can be inserted into a cell with Apps Script. You need to be familiar with the getRange() method: Link to Apps Script documentation - getRange(start row, start column, number of rows to get, number of columns to get — Alan Wells
Are you using an add-on to get notifications (assuming email notifications) with the submission contents, or are you just getting a notification that a submission has been made? — Gerneio
@Gerneio yes I am using yet another mail merge. sandy - Nope I am not familiar with the getRange(), thanks for the doc link will go through it. — Sanjay Achar
Can you share what you have done so far? How are you tying the addon "Yet another mail merge" to the submit trigger? Perhaps share some sample datasets of the submitted by the form as well as any code snippets you've tried. The reason why I am asking for this is to help us help you get a solution that is best molded to you, especially since there are several ways to go about this. The solution will whole-heartedly depend on your existing infrastructure, and this includes any add-ons that are in play. It's quite possible that the addon you are using might already have the solution for you. — Gerneio

Gerneio Gerneio · Accepted Answer · 2016-04-21T02:35:40

Ok after a bit of digging and testing, I believe I got a solution for you since you use the "Yet Another Mail Merge" (aka YAMM) add-on. Here it goes:

Firstly make sure your Form is setup properly and linked to a Google Sheet. After all questions have been created, add another column to your sheet, and call it 'Hyperlink' or whatever you please (just remember it for later). We will make use of the form submit trigger in the script editor along with some code.

Here's the code:

function onFormSubmit(e)
{
  var r = e.range;
  var v = e.namedValues;

  var link = v['Link'];

  // For testing purposes, this part was apart of my form, 
  // I'd assume you'd want to change it to something more 
  // usable in your case. Notice that I refer to the values 
  // by the name of the question they preside in.
  var friendlyName = v['Friendly Name'];

  var rngHyper = getCellRngByCol(r, 'Hyperlink');

  // See below for the meaning of the boolean
  addHyperlink(rngHyper, link, friendlyName, true);
}

// Will only return one cell no matter the range size.
// Perfect for onFormSubmit(e) use case.
function getCellRngByCol(rng, col)
{
  var aRng = SpreadsheetApp.getActiveSheet().getDataRange();
  var hRng = aRng.offset(0, 0, 1, aRng.getNumColumns()).getValues();
  var colIndex = hRng[0].indexOf(col);

  return SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), colIndex + 1);
}

// Add some form of hyperlink reference to one particular
// cell, passed as a range object
function addHyperlink(rng, link, name, useFormula)
{
  if (useFormula)
  {
    // If useFormula is TRUE, use Google Sheet HYPERLINK formula,
    // only if you are sure all URL's are formated properly,
    // and include HTTPS/HTTP/WWW. Also looks more pleasing in Google Sheet.

    var formula = '=HYPERLINK("<<URL>>", "<<NAME>>")';
    formula = formula.replace('<<URL>>', link).replace('<<NAME>>', name);

    rng.setFormula(formula);
  }
  else
  {
    // Else use HTML <a> tag with hyperlink referencing, which should transform
    // any URL passed as a clickable hyperlink within email. Not very visually
    // appealing in Google Sheet.

    var value = '<a href="<<URL>>"><<NAME>></a>';
    value = value.replace('<<URL>>', link).replace('<<NAME>>', name);
    rng.setValue(value);
  }
}

Then set the trigger, which will probably ask for authorization after saving:

Next, save the script and then put in a test submission through your form to see that the link is created properly, or as desired. Afterwards clear the rows of the spreadsheet (not the header) and remove all responses of the Form itself (not necessary, but keeps things organized for testing purposes).

Now, install the YAMM add-on. It should then add a new column at the end of your sheet called 'Merge satus'. Before setting up the email notification on submit, we need to create your email template. Open up GMAIL, create an email with the desired fields and layout and save it as a draft. Here's what I did as an example:

I'm sure you're familiar with how this add-on, works so I shouldn't need to explain too much here.

After the draft has been created and saved, go back to the Google Sheet attached to the Form. Go to Add-ons > YAMM > Configure form submission notifications. I chose the 'Notify one or more addresses of all responses' option which are tied to the 'To:' emails preset in the draft. Select your draft from the drop down, fill in sender name if needed, AND (very important!!) check the 'Wait until a specific column is filled before sending the email' check box. Make sure to select the Hyperlink column (or whatever you chose to name it earlier). Here's my setup for reference:

Save it, test it, and ta-da. This simple formatting for hyperlinks has been resolved! :D

Add custom Data to Google Sheet once Form is filled

1 Answers