Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Creating dynamic fields based on Google Sheets

  • November 18, 2019
  • 3 replies
  • 1133 views
P

As a marketing agency we spend a lot of time on reporting. For larger clients a custom dashboard is created, but this is not always feasible for smaller clients. So, we update them weekly with metrics such as impressions, reach and sales through e-mail.

Running reports from multiple data sources (Facebook, Google Ads...) and typing statistics in a mail is a waste of time. Ideally we want to automate this reporting by sending campaign managers weekly e-mails with the latest statistics.

Tools such as Supermetrics and Funnel can update metrics from almost all advertising channels periodically in Google Sheets. Unfortunately, it is not possible with Zapier to select the contents of specific cells and convert them to dynamic fields we can use in Gmail.

An example of this automation would be a weekly mail that contains the following, with dynamic fields as brackets:

--

Hi {client},

Quick update on your {channel} campaign. {Previous week} your {channel} campaign had {amount1} {metric1} and {amount2} {metric2}

--

Any ideas?


Best answer by PaulKortman

@pim just a little clarification as @ChrisP was pointing out, specifically when you said this:

Unfortunately, it is not possible with Zapier to select the contents of specific cells and convert them to dynamic fields we can use in Gmail.

That's not true.

It might not be straight forward or super clear, but I've often set up a Zap to grab a specific field from a sheet. The key is to have a column with the content you want to specify, for example in your case perhaps you want to look in the date field for this week, or the last 7 days, and then get the value from Column C D or E or all of them.

The way Google Sheet's "Find a row" works is all you need to do is provide a value you want to match in a column, and the whole row will be pulled in, if you only need one cell that's fine, you just specify in the email step which cell/column you need from the row that matched.


So this can get really "complex" but yet still be simple, say you have two different sheets you are trying to pull data from and then run some math (sum or average) on the results and present it to the client in an email. Here's how I would set it up:

  1. Trigger on date/time (ex: Monday's 8 am)
  2. Formatter step - Date - to get yesterday's date (Sunday)
  3. Formatter step - Date - to get 8 days ago date (Sunday)
  4. (possible steps to combine or format dates)
  5. Google Sheets find a row - specify the date as the column and then {{results from step 3}}-{{results from step 1}} as the value to look for
  6. (repeat step 5 as needed to get data from different sheets)
  7. (possible formatter steps to combine/average the data points)
  8. Gmail - send email -- use the data from column "clicks" or "spent" etc from step 5 (or the steps in 6 or 7 as needed) to flesh out the data in your template.


I've used this method to report activity that happened in the last 7 days... the cool part is by doing the date calculations within the Zap it will be the correct dates every Monday or when ever you have the zap set to trigger.

Let us know what other questions you have as you attempt to set this up, We're happy to help.


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

    3 replies

    AndrewJDavison
    Forum|alt.badge.img+11
    • AndrewJDavisonZapier Solution Partner
    • Zapier Solution Partner
    • November 18, 2019

    Hi @pim

    A (possibly complicated) way to do this would be a summary table in GSheets that uses lookup formulas to find the relevant cells from other sheets and pulls all the data onto a single row you can then retrieve with Zapier and map.

    You could also look to partially migrate to Integromat. They integrate with Google Sheets and have a "Get Range Value" search step, which as far as I know can be used to select a single cell 'range'.

    https://www.integromat.com/en/integrations/google-sheets

    Screen Shot 2019-11-18 at 12.09.30.pngYou could use a webhook in your zap to trigger a scenario in Intgeromat that finds the value in the cell you need and then returns it back to Zapier via a "Webhook Response":

    https://www.integromat.com/en/integrations/gateway

    Screen Shot 2019-11-18 at 12.11.07.pngThat response, complete with the data from the GSheets cell will then appear back in Zapier as output from the webhook step you created - and could then be mapped into your weekly email.


    Find out more and hire me: https://zapier.com/partnerdirectory/andrewjdavison (andrew@andrewjdavison.com)
    ikbelkirasan
    ChrisP
    Forum|alt.badge.img
    • ChrisPConnector
    • Connector
    • November 18, 2019

    @pim Not sure if I'm understanding you correctly but maybe this:

    As long as all the rows have the same headers and you get the full google row into Zapier (by automatic or manual trigger), you can then map the cells of each row to supported email integrations exactly as you wish above.

    When you customise email template in your actions step you can map each column of the sheet to each {example} you have above. Each new row added will trigger a new instance.

    If its a case of waiting until all rows contain all info then you can add a manual trigger step very easily for each row.

    You can also add a schedule or delay step thereafter to send whenever time of day/week you wish.




    AndrewJDavison
    PaulKortman
    PaulKortman
    Forum|alt.badge.img+10
    • PaulKortmanZapier Solution Partner
    • Zapier Solution Partner
    • Answer
    • November 22, 2019

    @pim just a little clarification as @ChrisP was pointing out, specifically when you said this:

    Unfortunately, it is not possible with Zapier to select the contents of specific cells and convert them to dynamic fields we can use in Gmail.

    That's not true.

    It might not be straight forward or super clear, but I've often set up a Zap to grab a specific field from a sheet. The key is to have a column with the content you want to specify, for example in your case perhaps you want to look in the date field for this week, or the last 7 days, and then get the value from Column C D or E or all of them.

    The way Google Sheet's "Find a row" works is all you need to do is provide a value you want to match in a column, and the whole row will be pulled in, if you only need one cell that's fine, you just specify in the email step which cell/column you need from the row that matched.


    So this can get really "complex" but yet still be simple, say you have two different sheets you are trying to pull data from and then run some math (sum or average) on the results and present it to the client in an email. Here's how I would set it up:

    1. Trigger on date/time (ex: Monday's 8 am)
    2. Formatter step - Date - to get yesterday's date (Sunday)
    3. Formatter step - Date - to get 8 days ago date (Sunday)
    4. (possible steps to combine or format dates)
    5. Google Sheets find a row - specify the date as the column and then {{results from step 3}}-{{results from step 1}} as the value to look for
    6. (repeat step 5 as needed to get data from different sheets)
    7. (possible formatter steps to combine/average the data points)
    8. Gmail - send email -- use the data from column "clicks" or "spent" etc from step 5 (or the steps in 6 or 7 as needed) to flesh out the data in your template.


    I've used this method to report activity that happened in the last 7 days... the cool part is by doing the date calculations within the Zap it will be the correct dates every Monday or when ever you have the zap set to trigger.

    Let us know what other questions you have as you attempt to set this up, We're happy to help.


    Need Additional help? Feel free to reach out here: https://zapier.com/experts/connex-digital/
    Danvers
    Terms & ConditionsAccessibility statement