Question

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

  • 12 July 2023
  • 3 replies
  • 107 views

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

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!

Thanks for the reply @SamB 

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

 

Appreciate your help! 

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!