I faced a challenge with Google Sheets last week. Took me two hours to resolve and ended up being the simplest solution I could imagine. Thought I’d share here to save other people some time.

The problem

I’m trying to append values to a URL. This is for a client who wants to automatically generate unique links to use in marketing emails to his customers. On the other side of these links is a Typeform survey, and he’d like to be able to match survey responses to names and email addresses while keeping the whole operation in MailChimp (versus, for example, using SurveyGizmo’s built-in mailer). This is possible with Typeform by using Hidden Values.

The solution

To accomplish this, I’m using Zapier to push all new MailChimp subscribers to a Google Sheet (Sheet 1). That’s STEP ONE.

STEP TWO is to push the first four columns (A through D) on Sheet 1 to another Sheet (Sheet 2). I did this by placing the following formula in cell A1:

=query(Sheet1!A:D)

Easy enough.

STEP THREE is to write a formula in the first blank column on Sheet 2 (column E) that appends values from Sheet 2’s four columns (A through D) onto the end of the Typeform survey URL.

Now, I didn’t want to have to manually drag whatever formula I wrote for one row down to all other rows in that column. In fact, I wanted the appending to be both automatic and to only run if there was data in column A. If column A was empty, then that entire row was empty (just a feature of my particular dataset) and I didn’t want any output into column D. I have particular reasons as to why, but this is a good general rule for keeping spreadsheets clean—especially if you are pushing that data to another application.

To accomplish what I just described, I used the arrayformula function. This function, also explained well here, expands whatever formula follows to all cells in the same column below where you enter the arrayformula operator. For example:

=arrayformula(B2:B+C2:C)

…placed in cell D2 will put B2+C2 in D2, B3+C3 in D3, and so on. This essentially automates the process of clicking-and-dragging cell D2 down to the bottom of your sheets, which just isn’t a smart way to set up such a process on a dynamic sheet.

That’s arrayformula, and it’s easy enough. But I ran into issues when trying to combine arrayformula with with concatenate. And I (thought I) needed to use concatenate to append values to the end of my Typeform URL.

I tried this at first, which did not work:

=arrayformula(if(A2:A<>“”,(concatenate(“https://mysurvey.typeform.com/to/OhWiaa?name=”,A2:A,“&cancer=”,C2:C,“&email=”,D2:D),“”))

The catch

The problem here was that mixing concatenate with arrayformula placed all cells in column A2:A into E2 (the cell where I entered this formula). The text in the cell looked  like this:

https://mysurvey.typeform.com/name=tombob&email=tom@test.combob@test.com

The same exact text populated in cell E3. I think you can see the problem: It was taking all values in column A, from every filled row, and putting them all into my URL and repeating that same exact URL onto every row in column E.

That’s not what I wanted. I wanted one URL per row that contained information only for the email subscriber in that row. I wanted this:

https://mysurvey.typeform.com/name=tom&email=tom@test.com

Anything else would not help me connect survey responses to the relevant email subscriber.

Ultimately, I discovered that concatenate just doesn’t work with arrayformula like I’d expect it to work. Some bug, maybe. Anyways, I Googled for two hours and discovered by accident that the ampersand (&) operator works like a charm! I’d always thought ampersand was just a longwinded way to do what concatenate does, but in Google Sheets, at least, ampersand is slightly more flexible than concatenate.

The formula that finally worked:

=arrayformula(if(A2:A<>“”,(“https://mysurvey.typeform.com/to/OhWiaa?name=”&A2:A&“&cancer=”&C2:C&“&email=”&D2:D),“”))

With the unique links being created automatically, I now use Zapier to push data from Sheet 2 back to MailChimp, where it automatically updates all records with the new appended URL.

Voila!