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

You 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

That 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.

@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.

@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.