Automating spreadsheet function from a Google form

Question

Let me start off saying that I know absolutely nothing about how scripting works. I found assistance on making the spreadsheet formula work via Google Groups.

I have 2 Google Sheets:

First one is a contact list [name and address removed for privacy]. Generally it would consist of name, address, city, zip code.

Contact List: https://docs.google.com/spreadsheets/d/19uDa5aKp2KlS-6Lfe0jTehTWDcLgBHeisKnsJzd4cP0/edit?usp=sharing

The 2nd list is a list of zip codes within a specific mile radius of a city.

ZIPS: https://docs.google.com/spreadsheets/d/1S7n9NBE_CRSgx5vjCY5CgICchsc8NelsN7FXM6CkooY/edit?usp=sharing

Using the sheet ZIPS I put this syntax into B1:

=query(importrange("19uDa5aKp2KlS-6Lfe0jTehTWDcLgBHeisKnsJzd4cP0","Contact List!A:G"),"select * where Col7 matches ' " &join("|",A1:A16) & " ' ")

[A1:A16 would be changing everytime, maybe a wildcard could be used for column A]

What that does is pull all the contacts from the Contact List sheet and import them to the ZIPS sheet. Thats the back end way to do it.

My question is can this be done through a Google form or some other front end form? I will always have the same Contact List but my ZIPS search would change every time.

If I could have a front end form where I could input the zip codes that I want matched from the Contact List and exported, that would be perfect.

Does that make sense?? Thank you for your help.

Answer 1

I followed your links and had a quick look. I am not completely sure I follow what you are after.

What I did was add a Form to your https://docs.google.com/spreadsheets/d/19uDa5aKp2KlS-6Lfe0jTehTWDcLgBHeisKnsJzd4cP0/edit?usp=sharing

You can see this by;

• form>edit form
• form>go to live form

When the form submits it adds information to the spreadsheet 'Form Response 1' You can rename this, a quick piece of advice is make changes to the form not the spreadsheet if you want to change things that the form pushes to the sheet.

I then added a sheet to your https://docs.google.com/spreadsheets/d/1S7n9NBE_CRSgx5vjCY5CgICchsc8NelsN7FXM6CkooY/edit?usp=sharing I tweaked your formula a little to reflect the different range.

As a last help, you mentioned

[A1:A16 would be changing everytime, maybe a wildcard could be used for column A]

You would have to be careful with this because if you select the column A:A, your query will add a delimiter '|' for every row in the column so you will get value1|value2|...|value16|||||||||||||||||||||||||||||||||||||||

teatimer is right about writing code, I hope what I have given helps. It should be quite easy to do but you may need to tweek things carefully to get it to work as you like.