How to automate data transfer from Google Sheets to Monday.com using Zapier

Hi ​@ChrisHG87 

Help links for using Looping in Zaps: https://zapier.com/apps/looping/integrations#help

For us to have more info, post screenshots showing how your Zap steps are outlined and configured in the CONFIGURE tab while in EDIT mode with the field mappings visible.

NOTE: When testing the Looping step, only the first loop iteration will be tested.

Hi Troy,

Thanks for the reply.

I’ve skipped the Monday step as it’s previously worked, but hopefully the below can help. Just to add, this was all done via Copilot.

Thanks

​@ChrisHG87 

The screenshots do not show how all of your Zap steps are outlined and configured.

You likely do not need a Code step.

IF the XLS is a flat file format (e.g. headers as first row), then get the CSV version of the GSheet.

Help: 

You can use the Files by Zapier app to get the CSV file rows: https://zapier.com/apps/files-by-zapier/integrations#triggers-and-actions

Use Looping (Create Loop from Line Items).

Map data in to the Looping step from the Files by Zapier step.

Then map data out from the Looping step to Monday.

NOTE: When testing the Looping step, only the first loop iteration will be tested.

Help: https://zapier.com/apps/looping/integrations#help

Make sure to test LIVE with a XLS file that has a few rows.

Check the Zap Runs history: https://zapier.com/app/history/

Hi Troy,

So I remade this but still don’t seem to be getting anywhere. 

I get to the point where the test runs with static values, but as soon as I add in the fields and try a test, I’m getting the below, even though it has a value that is pulling from the Loop items. Any ideas?

​@ChrisHG87 

In the Files by Zapier step, you need to map the download link to the CSV version of the file, rather than the file objects.

Help: 

Hi Troy. But what if that file is different each time the zap runs? The aim is to upload a new file each day so therefore the file name will change each time - does this matter? Thanks, C

​@ChrisHG87 

The purpose of mapping a dynamic variable is so that each Zap Run uses the CSV download link for that file.

Previously provided help links to review:

https://community.zapier.com/show%2Dtell%2D5/how%2Dto%2Dget%2Dthe%2Dcsv%2Dversion%2Dof%2Dexcel%2Dgsheet%2Dxls%2Dfile%2Dusing%2Dgdrive%2D14686?tid=14686&fid=5

https://community.zapier.com/show%2Dtell%2D5/gdrive%2Dfile%2Dlink%2Dformats%2Dgsheets%2Dgdocs%2Dgslides%2D25077?tid=25077&fid=5

​@ChrisHG87 

The mapped variable from GDrive for the CSV version of the spreadsheet should look like this: 

Hi Troy,

With “Find a File”, the file name will be different for each CSV that gets added to Google Drive, therefore I don’t know how to map to something that will change every day?

The key point is that I add a CSV to the folder, the Zap finds it and pulls the data from that particular file. Then the next time, the file will be different. Therefore the dynamic variable being mapped to a particular file won’t work will it?

I’m confused? 

​@ChrisHG87 

Why are you now trying to use GDrive “Find a File” as a Zap action step?

Your previous screenshots show GDrive as the Zap trigger step: GDrive - New File in Folder

Every new CSV/XLS file added to the GDrive Folder in Zap step 1 will return the same data points to map to Zap action steps.

If you are looking to hire help with your project, there is a directory of Zapier Partners: https://zapier.com/partnerdirectory

Hi Troy,

Apologies for the confusion.

In “2: Convert CSV file to line items”, the “CSV File” field, can you advise what I need to map or what I need to type/find?

At the moment, “Export links Text CSV” isn’t one of the fields I can find?

Thanks,

C

​@ChrisHG87 

Did you review both of the help links below? (If no, do that.)

Are you uploading a CSV or a XLS file to the GDrive Folder?

Post a screenshot of the top 3 rows so we can see how the file is structured.

Post screenshots showing the DATA OUT from Zap step 1, so we have true context about the data you are trying to use from GDrive.

https://community.zapier.com/show%2Dtell%2D5/how%2Dto%2Dget%2Dthe%2Dcsv%2Dversion%2Dof%2Dexcel%2Dgsheet%2Dxls%2Dfile%2Dusing%2Dgdrive%2D14686?tid=14686&fid=5

https://community.zapier.com/show%2Dtell%2D5/gdrive%2Dfile%2Dlink%2Dformats%2Dgsheets%2Dgdocs%2Dgslides%2D25077?tid=25077&fid=5

Hi Troy,

I’m always going to be uploading a CSV. Regarding the data, I can’t really share as it’s specific to customers, but it’s just a number of text fields that I’m mapping to Monday.

Regarding Step 2: there was no “Export links Text CSV”.

Copilot will get it so every field maps with static data, but it won’t publish with the variable, so it’ll remove it to get it past the test, but then when I add back in, I get the “Item Name” issue, but the spreadsheet I’m using for the test has multiple rows of valid data.

​@ChrisHG87 

In your screenshot you have a trailing / that needs to be removed for the CSV File input field.

The best way for us to have true context is to show screenshots of these:

• how your Zap steps are outlined
• how your Zap steps are configured in the CONFIGURE tab while in EDIT mode with the field mappings visible
• DATA IN from the TEST tab
• DATA OUT from the TEST tab

Hi Troy,

I’ve screenshotted everything and uploaded this to Google Drive - I’ve sent you the link privately. I’ve black out private data but this is just text based content.

Hopefully this can help.

Thanks,

C

​@ChrisHG87 

See screenshots below.

Make sure to remove the trailing / in the CSV File field input value.

Previously provided help links for using Looping: https://zapier.com/apps/looping/integrations#help

For the Looping step, for the Line Items, you need to map each desired data point from Zap step 2 to its own Values to Loop key/value set.

e.g. If you want to map 5 CSV columns from Zap step 2, then in the Zap step 3 Looping, there should be 5 Values to Loop lines.

Such as: Company Name, Country, Industry, etc.

Click the button [Add Value Set].

EXAMPLE
Left side: Company Name

Right side: Variable for “Company Name” mapped from Zap step 2

TIP: Don’t forget to remap your variables from Zap step 3 (Looping) to Zap step 4 (Monday).

The Export links fields only exist for native Google Sheets files, not for an uploaded .xlsx. Drive treats an uploaded xlsx as a plain binary file, so it has no CSV export link at all, which is why that field never appears in the mapping list no matter what you pick.

Two ways around it.

Convert it after it lands. Add a Google Drive step that re-uploads the new file with the convert option turned on, which produces a real Google Sheet, and take the CSV export link from that step rather than from the trigger. The trigger feeds it the file id, so nothing is hardcoded.

Or skip Drive for the parsing entirely. The file is arriving by email anyway, so trigger on the Gmail message with an attachment, pass the attachment straight into Files by Zapier to get line items, then loop into Monday. Fewer moving parts and nothing that depends on the file name.

On the daily file name worry: that is not actually a problem. New File in Folder hands you the file id, and the id is the stable handle everything downstream should use. The name only matters if something in the chain is searching by name, which is why Find a File was pulling you sideways.

Hi Troy,

Ah this makes sense. I’ve got Steps 3 and 4 mapped like this now:

However, the file has 10 lines and it’s only pulling the first. In Step 3, I mapped it to these:

Any ideas?

Hi ​@Troy Tessalone,

Just wondering if you had any ideas here? I feel like it’s almost working, but I’m keen to not throw this at Copilot in case it messes things up.

Thanks,

C​​​​​​​

​@ChrisHG87 

See screenshot below.

In Zap step 3, turn OFF, the “enhanced field mappings”, and remap the variables for the Value to Loop.

Why?

Because the [0] in the mapped variables currently indicate only the first array item, rather than all array items.

Hi Troy, 

This works!!!! Thanks for all your help.

2nd issue - I need to make the folder in Google Drive restricted, but doing so seems to stop it working. I’ve refreshed the connection, but no joy.

Is there anyway around this?

C

​@ChrisHG87 

Help links for using GDrive: https://zapier.com/apps/google-drive/integrations#help

Prerequisites

To use the Google Drive app on Zapier, you must have:

• A Google account with access to Google Drive.
• You also need to have view or edit access to any files or folders you do not own that you want to interact with.

My files are not triggering my Zap when moved into a folder

If a file is moved into a folder containing more than 500 files, it may not trigger the Zap. Zapier checks the folder using the file's creation date/ Google's API does not provide a "move date". If 500 files with newer creation dates exist in the folder, the moved file's original, older creation date caused it to be excluded from the 500 newer files Zapier checks.

To prevent this issue, keep your trigger folder size below 500 files. Zapier strongly recommends implementing a processing workflow where files are automatically moved out of the trigger folder after they have been processed by the Zap.

Why can't I see my desired folder in the dropdown menu?

Google Drive requires you to be the owner of a given folder in order for you to be able to interact with this folder in Zapier, such as by uploading a file to the folder or triggering on a new file added to that folder.

Hi Troy,

Thanks for the quick reply.

At the moment, there is only my test file in the folder, but even if I’m adding one file per day, that will still be well within the 500 limit. It’s also my folder that I have created on my own drive, I just need to restrict it for Google Automations.

I receive an email from LeadForensics each day that includes the CSV as an attachment - at the moment, I’m manually adding this to my drive, but GMail has an automation that will auto upload it to the Zap folder, the folder needs to be restricted though for it to work. 

Would there be a different way of getting round this? The last bit I’m stuck on is getting the file from my Gmail into the folder automatically.

Thanks again for all your help.

C

​@ChrisHG87 

If the GDrive Folder being used is on your Drive (not a Shared Drive), and if you only have access to that GDrive Folder, then why does that GDrive Folder need to be restricted?

Hi Troy,

As the automations won’t work to auto-upload if the drive isn’t restricted. Is there no other way I can use Zapier to get the folder from Gmail to Gdrive?

Thanks,

C