Skip to main content

Hey team, 

I was wondering how can we set up an automation with the following: 

  • Send monthly reports via email to our client about how many have booked and cancelled an appointment. These reports will be tracked using a Google Sheet. 
    • This should be sent every 1st of each month, then we will need to duplicate the template worksheet and rename those duplicates with the corresponding month and year. 
  • We need to make sure that these duplicates has the same header as the template, also is there anyway that Zapier has an action to rename the worksheet? 
  • Once this has been created, how can we automate the Zapier to make sure that new records will be tracked on the new worksheet for that corresponding month. Is it also possible that the email that is being sent monthly will have the updated link of the new worksheet for that month?

I was able to do the following triggers and actions: 

Trigger: Schedule by Zapier (every month, 1st of the month )

Action: Send Outbound Email in Email by Zapier (added the spreadsheet link to the body of the email)

Action: Copy Worksheet in Google Sheets (this is to duplicated the template worksheet to create a new one every 1st of the month) -- the problem is we cannot rename the newly created copy and how can we automate the new records to be sent on this new copy?

 

Thank you in advance!

Hi @thefollowupagency 

Good question.

I’d recommend using Airtable instead of GSheets.

Airtable has Views which are segments of data. (sort, filter, group, show/hide fields, reorder fields)

Airtable Views can be shared via read only links.

Airtable has Formula fields that can be used to determine the Month and Year for a Date.

The concept would be to create 1 View with Filter conditions such that the records in the View are for the previous month.

 

Hi @thefollowupagency 

Good question.

I’d recommend using Airtable instead of GSheets.

Airtable has Views which are segments of data. (sort, filter, group, show/hide fields, reorder fields)

Airtable Views can be shared via read only links.

Airtable has Formula fields that can be used to determine the Month and Year for a Date.

The concept would be to create 1 View with Filter conditions such that the records in the View are for the previous month.

 

But in case we don not have an Airtable account and would like to stick with Google Sheets, email and the rest.. is there anyway that we can make the above possible? 

@thefollowupagency 

Help article to reference:

 

You may need to use GDrive triggers/actions as part of your Zap steps: https://zapier.com/apps/google-drive/integrations#triggers-and-actions

@thefollowupagency

Help article to reference:

 

You may need to use GDrive triggers/actions as part of your Zap steps: https://zapier.com/apps/google-drive/integrations#triggers-and-actions

Hey Troy, the article you provided has an update: 

The Original Post below should no longer be needed - the Zap Editor can now handle these dynamic mappings. And when I try to click the link for this: Mapping fields to dynamic dropdown menus (like in Google Sheets), the page is not working even after refreshing the page. 

 

Can you help me with the triggers and actions for me to set this up? 

 

Thank you so much!

 

 

@thefollowupagency 

If you’re looking for help, consider hiring a Certified Zapier Expert.

Terms & Conditions