Best answer

Google form responses - send responses from specific clients to specific sheets

  • 11 August 2022
  • 4 replies
  • 32 views

Hi amazing Zapier community, I like to know if I can use Zapier for this task:

We have a call evaluation google form that each rep must fill out after each cold call. This google form automatically creates a response google sheet, with every new call submission, a new row with several cells.

We want to create an automated 2nd google sheet for our clients that draw only certain response sheet cells for each call, each into a new google sheet or new tab.
In the end, each client would have a separate overview google sheet.

Can this be accomplished with Zapier and if so how? Thanks a lot for any tips. Best regards Stefanie 

icon

Best answer by Danvers 11 August 2022, 14:03

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Hi @CreativeAutomation, thank you very much for your reply. I probably DM you, because I am a newbie on this topic. Stefanie

Userlevel 1

Hi @Stefanie 

Here is a starter if you go into the document App Script writer you need to write a script that says if a cell is x then copy to my second sheet:

 

// Open the two sheets to interact and set the data range

var ss = SpreadsheetApp.openByUrl(sheetURL).getSheetByName("YourSheetName");

var ssnew = SpreadsheetApp.openByUrl(sheetURL).getSheetByName("YourSecondSheetName");

var data = ss.getDataRange().getValues();

 

 // Now work through sheet looking for matches, the line below will look through each row of the first column 
 // until it finds a blank row, the datarange was set above to the variable data

 // the row and column is labelled as data[row,column]  - column [0] is column 1 or A on your sheet

  for (var i = 1; i < data.length;i++) {

    if(data[i][0] == ""){ break;}

 

    var n = data[i][3]; // Select the column that needs to be looked at in script [3] means column 4 or D

    // below you need to say what you are looking for  between the ‘’ in this case I am saying if this cell is blank

    if(n==''){

 

   // then decide what to do  - below I am looking up something from the same row from column 10 or J 

    var fileName = data[i][9];

  

  // now add that filename to a new cell on my second sheet

    ssnew.getRange(row,column).setValue(fileName) 

    }

  }

If you need some specific work done then DM me please but hopefully that helps a bit.

Hi @Danvers 

Thank you very much for getting back to me. Yes, correct. I want only some responses to be sent to newly generated sheets once there is a new response.  I will certainly look into your suggestion.  Thanks again. Stefanie

Userlevel 7
Badge +12

Hi @Stefanie!

It sounds like you want to send responses from specific clients to specific sheets (in addition to the main one) is that right?

I think that the most cost-efficient solution for you will actually be to use Google’s built in functions, rather than Zapier. There are a few different ways to do that, I found this guide on the internet (note, this isn’t officially endorsed by Zapier, I saw it and thought it looked helpful for you!) that explains 3 different methods that you could use to do that. 

If you did want to do it through Zapier, the easiest way would be to use Paths and create a different Path for each client. You can also use a Filter step to generate if/then logic in a similar way, but it would require a different Zap for each client. 

If it were me doing the same thing, I’d definitely opt for using Google’s functions. They can be fiddly to get the hang of, but when you do they’re really handy!