About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

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

Zapier Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

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.

  • Home
  • Get Help
  • How Do I
  • How to create a daily log of open customer service tickets from Google Drive to Google Sheets?
Question

How to create a daily log of open customer service tickets from Google Drive to Google Sheets?

  • 12 July 2023
  • 3 replies
  • 76 views
pjdoubleyou

Hi all - new to Zapier, feel like I’ve almost got this and yet, I’m still so far away! Here’s the deal…

  • Every night I get a count of open customer service tickets dropped in Google Drive
  • I want to copy the number of open tickets into a new row in a Google Sheet 

I’m creating a daily log of open tickets. So, everyday I’d open the Google Sheet and see a new row with the prior day’s date and the number of open tickets. Seems simple, but I can’t quite come up with the right combination of triggers and actions. 

Appreciate the help! 

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

SamB
Rank
Userlevel 7
Badge +11

Hello and welcome to the Community @pjdoubleyou! 👋

You mentioned that you currently get a count of the open customer services tickets added into Google Drive. Is the count number added into a Google Sheets spreadsheet that’s on your Google Drive? 

Or, do you get a list of all the open tickets added into a CSV or Excel file? In which case is it that you’re wanting to count how many rows are filled in that file and add that value to a spreadsheet along with the prior day’s date?

Sorry for my uncertainty here. If you can share some further details and screenshots showing what information that is added into Google Drive that’ll give us some more context, and allow us to better advise on the best approach. Please remove/hide any private information (like names, email addresses etc.) from any screenshots before sharing here.

Looking forward to hearing from you on this!

pjdoubleyou

Thanks for the reply @SamB 

I get the open tickets as an Excel sheet. It looks like this:

 

Appreciate your help! 

SamB
Rank
Userlevel 7
Badge +11

Thanks for getting back to me @pjdoubleyou!

Hmm, ok it doesn’t look like the Zap is going to be able to read the contents if it’s an Excel file that’s on Google Drive. If it was converted to a Google Sheets spreadsheet though then you could potentially trigger when a New Spreadsheet is added and then follow it up with a Get Many Spreadsheet Rows action to access the contents of the spreadsheet. That would allow you to then select the value from cell A2 of the spreadsheet. Then you’d use a Formatter (Date /Time > Add/Subtract Time) action to get the previous day’s date. And then follow that up with a Create Spreadsheet Row action to add the total and date to the desired Google Sheet.

That would involve opening up the Excel file in Google Drive and re-saving it as a Google Sheets file though, which isn’t ideal. Assuming these Excel files are automatically added into Google Drive by another app, are you able to change the settings in the app to get it to add the the file as a CSV instead? Just thinking that maybe if it was added as a CSV file you could potentially use a Formatter (Utilities > Import CSV File) action to access the contents of the CSV that way.

 

To get the previous day’s date: On the Formatter action in the Input you’d need to use the {{zap_meta_human_now}} command to give it today’s date. Then use the Expression of -1day to get the previous day’s date. And select whatever To Format you’d like to convert it into for example DD/MM/YYYY:
fb7bd68f5a1a04742a3baaf0a99b95c1.png

Which would output 12/07/2023 if run today:

5c7e304aa4f5e947fc3c9ac4f11e4092.png

Hopefully that helps to get you started. Please do keep us in the loop on how you get on!

Terms & Conditions