Pulling 100K+ of data from a Google Sheet and filter it customer name

Happy Friday, ​@Mahran—welcome to the Community! 🎉

Just to double-check, is it that the spreadsheet itself has 100,000 rows that you’d want to search and extract certain customer data from? Or are you wanting to extract 100,000 rows from the spreadsheet for each customer?

The Lookup Spreadsheet Rows (Advanced) Google Sheets action is limited to a maximum of 500 rows. So while it wouldn’t be able to extract 100,000 rows of data from the spreadsheet, you could use it to get up to 500 rows of data for specific customers. 

Can you tell us a bit more about how the data is added to the spreadsheet?

I’m wondering if a workaround could be to have the data automatically added into separate spreadsheet for each customer based on their name/ID so that going forward you could just search for the file using a Find a File (Google Drive) and get the CSV export of it that way. See this guide on how to access the CSV file format of a file on Google Drive:

Looking forward to hearing from you!

​@Mahran 

Instead of using the native action to get google sheet rows in zapier which is limited to 500 rows, you can use API request Beta making a custom api request, it might allow you to get upto 10,000 rows, 

A workaround you can use is using Appscript in the GoogleSheet, you pass the name you want to search to the Appscript, It will look for the customers for that name and respond with the rows of customers matching the name etc, you might have to custom code this depending on your requirements.

 

Hi ​@Mahran 

You would probably be better served using a proper database app than trying to use a GSheet for that amount of data.

Database Zap apps: https://zapier.com/apps/categories/databases

You can then send the data to a GSheet for the CSV requirement.

Thanks All for the feedback. To clarify, the data lives in DOMO and im trying to automate a flow to pull data from DOMO, filter it by customer name and send it by email on a weekly basis. Limiting the data is not an option as each customer expected to recieved their full data each week.

While I have considered using other database apps, they still require subscribed accounts to be able to deal with large data and process it as inteded. I have tried using Python code to pull the data from domo via API but then I faced limits around filtering it and generating CSV files for emailing purposes. 

​@Mahran 

Seems like you would want to do filtered queries.

e.g. If you have 3 customers, then query only for Customer A data, then query only for Customer B data, etc.