Question

How do I connect Sheets with Drive to see if a file exists?

  • 10 October 2022
  • 8 replies
  • 314 views

Userlevel 1

I want my invoice Google sheet to automatically see if the reciept is in my Drive folder.

 

I want to create a column with the filename (or regex).

I want Zapier to compare that column with files in a drive folder then give a tick in that row if it is there.

 

If Zapier can do this, it will save me hoursx10.


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

8 replies

Userlevel 7
Badge +12

Hi @JimFishtin, welcome to the Community!

Let’s start with that first step: the invoice Google Sheet. Could you share some more information about the invoice? Specifically how is the sheet formatted, is it formatted the same each time and do you get a new sheet for each invoice, or is it one sheet that’s updated as invoices come in?

I hope those questions are clear, let me know if you’d like me to clarify anything!

Userlevel 1

Hi thanks for replying.

 

The sheet will stay the same. It’s basically a bank balance sheet.

The task I do manually is make sure each cost has a receipt. I look at each item, search google drive for the file and I put a tick next to each cost to show it has a receipt/invoice in Google Drive.

All receipts are put in a google drive folder throughout the month and I do the book keeping at least once a month.

If Zapier could search google drive based on an Unique ID in my bank balance sheet and tick that row, it would save me a lot of time.

Here’s an example. (Hidden some columns for privacy)

 

Userlevel 4
Badge +7

Hey again @JimFishtin !

 

Assuming the ID you are trying to match is the title of a file or a folder inside of your Google Drive account, I think you can do this.

We need a way to trigger the Zap to do this check when you are ready for it.  The easiest way would be for you to add a column to your sheet for this.  Name it whatever you wish and when you are ready for the automated check, add any value to it.  You will set up the Google Sheet New/Updated Row trigger to watch that specific column for changes and trigger if there are.  The Zap would look something like this:

Google Sheets New/Updated Row trigger => Google Drive Find File/Folder action => Filter by Zapier action => Google Sheets Update Row action

 

For the Find File/Folder step, you would pass your Unique ID from the Google Sheets trigger and also ensure the “Success if nothing found” option is set to True.

For the Filter, you would configure it to only continue if the Find step is a success and “ID” “Exists”

For the Google Sheets Update Row step, pass the Row ID from the trigger so we update the correct row and pass the value of TRUE into the cell that contains the checkmark type in your sheet, which will turn it into a check.

 

Check that out and let us know how it goes! =)

 

Userlevel 1

Hello

 

Thanks for the walk through.

I got stuck here searching for the file name. How do I pass the unique ID into it?

This was the example I tried but then it will only ever look for files with that name?

How does it search that column and cross reference with files in the folder?

 

 

Userlevel 1

I also got stuck in this phase.

It seems Zapier wants to add a whole row rather than update one cell in the row.

 

 

Userlevel 7
Badge +12

Hi again, @JimFishtin!

Let’s start with your first question - what should you use to search for the file in Google Drive? I think when @RALaBarge said to use the ID he was referring to the fact that you said:

If Zapier could search google drive based on an Unique ID in my bank balance sheet and tick that row

Is there already a column in the Google Sheet that has a value you can use to search for the relevant receipt in Google Drive? If so, if you select use that column in the Find File/Folder step (eg the Filename step) then the Zap will use the value from that column each time the Zap runs, but it will take the value from the Row that triggered the Zap. For example, when you update Row 1, it will look at what’s in the Filename column for Row 1 and when you update Row 2 it will look at the filename in Row 2. Does that clear things up?

 

For your second question, the action says Update Row because you can update the whole row but it will only update any columns that you add information for. If you only add something in the Receipt column, that’s the only cell in that row that will be updated. It looks like you’re getting an error on that step because the wrong information is being used for the Row. In the Update Row action, for the Row select ‘Custom’ and then find the Row ID field from the trigger step. That will make sure that the Zap updates the same row that triggered the Zap. 

 

Could you give that a go and let us know how you get on? Thanks!

Userlevel 5
Badge +7

@JimFishtin just checking in to see how you managed with Danver’s walk through above. If you need any more support, give a shout and we’re happy to continue supporting you! 

 

Best,
Rachael

Userlevel 1

@Rachael S Thanks for checking in. Not had a chance to get into to it yet, but it is starred in my inbox.