Question

Possible to update multiple rows in a spreadsheet from a CSV file?


I have a csv file generated periodically in phantombuster, that i’d like to import into a spreadsheet so that said spreadsheet is always populated with the newest output from phantombuster.

So i have been using “new output in phantombuster” as the trigger and i thought using the google sheets “update spreadsheet row(s)” would be sufficient. But it appears that when i add “row number” at the end of the google sheets zap, i can only enter singular row values.

I thought that this was the exact use case for this sheets zap? Or do i need Looping or somehting else?

Thank you!


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

16 replies

Userlevel 7
Badge +11

Hi @Anders Nedergaard! 👋

The Update Spreadsheet Row(s) action is indeed able to update multiple rows in Google Sheets. But you’d want to pass the Row Numbers and other information from the CSV file as line items. That way the action will be able to process each row individually instead of reading it as a single row to be updated.

Does the PhantomBuster app supply the CSV file information as line items?

If not, you may need to use a Formatter (Utilities > Import CSV File) action (see: Import CSV files into Zaps) to get the rows of the CSV file output as individual line items that can be used in the Update Spreadsheet Row(s) action. 

Hope that helps to get you pointed in the right direction! 🙂

Yes, it does seem to be organized as line items. I have a main grouping called resultobject (corresponding to “dogs” in the attached article and then numbered groups of data attached to facebook users.

 

the google sheets zap also seems to recognize them as line item data, as it opens the “rows” dialogue. where i can map the various types of metadata. But i am still only getting the first line of data out and i still don’t know what data to to fit into the “rows” descriptor in the google sheets zap.

Userlevel 7
Badge +14

Hi @Anders Nedergaard 

Best to provide detailed screenshots with how your Zap steps are configured in order for us to have full context, thanks.

sure. I just had to blind them as some of the are sensitive. These are four screendumps showing in part the data organization (as, what i would believe is line items) and the google sheets dialogue.

Userlevel 7
Badge +14

@Anders Nedergaard 

Row Number is a required field that expects the number of each row in the GSheet in order to update.

If you don’t know these then you’ll have to configure the Zap like this:

  1. Trigger: PhantomBuster - New Output
  2. Action: Looping - Line Items
    1. Max of 500
  3. Action: GSheets - Lookup Row
  4. Action: GSheets - Update Row
    1. Map the Row/ID from step 3

 

thank you so much for your answers. I really appreciate it.

The phantombuster output is a list of members from a facebook group. Some of the members will eventually disappear from the group and hence the csv. Doesn’t that mean that the update will fail to update the members that have disappeared as the lookup won’t be able to find them?

 

Userlevel 7
Badge +14

@Anders Nedergaard 

You can add a Filter step as shown below.

  • Action: GSheets - Lookup Row
  • Action: Filter
  • Action: GSheets - Update Row
    1. Map the Row/ID from step 3

I’ve tried multiple iterations of this now and have this anoying sense that I’m missing something obvious. Would it be possible to pay one of you guys to look at it? I already wrote some of the zapier experts accessible through the zapier website but got no reply.

Userlevel 7
Badge +11

Sorry to hear that you’ve not had a reply from those experts yet, @Anders Nedergaard. I’m not available for hire, but our Zapier Experts like @Troy Tessalone would be. 🙂

In the meantime, if you’d like to share some further screenshots here showing the updated setup, I’d be happy to take a look and help troubleshoot further. Looking forward to your reply!

Sure, but what screenshots would help?

 

As far as the lookup suggested above, i got stuck on what exactly to look up. If the facebook group have gotten new members I wouldn’t know what to look up to have the zap process them and put them into the google sheet, using the lookup suggested above.

 

Would it work to simply add a column with row numbers into the CSV data and feed that into the row number field? or use that as the reference for the lookup? I’m still trying to understand what the row number field does. It seems (as per the icon next to it), to only accept static numerals and not variables. I’ve tried adding number series separated by spaces or commas, just like i have tried to indicate a number range/series by double punctuation marks, or a hyphen. All of those either return an error or just the first row of data in the csv.

Userlevel 7
Badge +11

Thanks for getting back to me, @Anders Nedergaard

Ideally we want some screenshots showing the current set up of the actions in the Zap so that we could check where it’s currently at and better identify what might need adjusting to get things working. But I’m happy to talk it through here if that’s easier.

The row numbers are the unique identifier that Google Sheets needs in order to know which row to update. The Row field can accept both static numbers and dynamic values passed in fields from other triggers and actions in the Zap.

As you’re wanting to update the rows, but don’t have the row number supplied by PhantomBuster you’ll need to use a Lookup Spreadsheet Row action in order to locate the relevant row to update. And as you’re updating multiple rows the lookup and update actions would need to go within a Looping by Zapier loop as Troy mentioned earlier. So the Zap would have the following trigger and actions:

  • Trigger: New Output (PhantomBuster) - triggers when a new list of members is sent to the Zap.
  • Action: Create Loop From Line Items (Looping By Zapier) - this uses the line items received from the PhantomBuster trigger as the “list” of items to run through each individually.
  • Action: Lookup Spreadsheet Row (Google Sheets) - takes a search value from the line items pulled through in the Looping by Zapier action to search for a single row that matches.
  • Action: Update Spreadsheet Row (Google Sheets) - takes the Row number from the row that was found by the previous action.

The loop action in the Zap will then run through each line item it receives, look for a matching row and update the row.

I don’t have a PhantomBuster account so I can’t show you exactly how the set up would look like. But essentially you’d select the line item fields that you’d want to search for and populate Google Sheets with. For example:
eb0a3a2681076138d0be8577295ff023.png
Then in the Lookup Spreadsheet Row action, you’d select the column that contains the value you’re searching by. For example if the Name was unique then I’d set the Lookup Column to be the Name column. And set the Lookup Value as the Name field that’s output by the Looping by Zapier action:

863c57c3ff744f156cee78eccf3a0d1b.png

For your case you’d want to use something that’s specific to the member and doesn’t change, like the Profile URL perhaps?

Then in the Update Spreadsheet Row you’d select the Row number that is output by the previous Google sheets action. This will ensure that the Zap will update the row if found in the previous action. And then you’d select any fields that you wish to update on the row as appropriate. For example if I wanted to leave the Name field as it is but update the Description column in the row I’d select the Description field that’s output by the Looping by Zapier action:

30504a809f56836a26137c1a4a2ec37b.png
I’d also recommend checking out our Loop your Zap actions guide for more details on how to work with loops in Zaps. 

Can you try amending the Zap as per the process outlined above and let us know whether that gets it working? 

can you elaborate on the lookup step, please, because I don’t understand how its supposed to work.

I want the spreadsheet to contain all present members of a facebook group.

So, if i use a lookup to look for rows to update, by name, mail or facebook UID, how can it update the spreadsheet with new members of the facebook group? It can’t look up a person that is not already in the list? Am i totally misunderstanding what you are writing or missing something you’re saying?

 

And I REALLY appreciate you taking the time to help out!

Userlevel 7
Badge +12

Hi @Anders Nedergaard!

Can we take a step back in the process to make sure that we’re on the same page? I have a couple of questions:

  1. Does the csv file have ALL members in the group (ones that were previously there and ones that have been added), or just new ones?
  2. Do you want to add a new row to the Google Sheet for each new member or do you want to add a row for each new member and update existing rows?

If the csv only has new members on it, you can use the Create Spreadsheet Row(s) action to add a new row in the sheet for each line item in the csv file from Phantombuster. 

 

If the csv has members that are already on the sheet, let us know exactly what’s in the csv and what you want the Zap to do for existing members and new members, and we can work through a possible Zap with you 😊

 

​I hope that's clear, please let us know if you have any questions!

Hi, Danvers. Thanks for chiming in ;o)

  1. The output from phantombuster contains present members only and that is what i need in the spreadsheet. I want the spreadsheet to contain only present members. I don’t want previous members in this sheet.
  2. uhm. your question is based of a premise of of new members being added, which is kind of not what we got here or what i want. But to reframe, I guess I want to have rows created for each member, overwriting previous data in the spreadsheet, if there are any, so that the spreadsheet always contains a list corresponding to the present members in the facebook group if that makes sense.

Hope that makes sense to everyone. English is not my first language.

Anders

Userlevel 7
Badge +12

That’s perfect, thanks for explaining that, @Anders Nedergaard!

If you want the Google Sheet to completely match the output from phantom buster each week, we can do that! We need a different approach though. 

If the Google Sheet has the same headers as the Phantombuster csv, this might be simple(!) You can use the Google Drive action Replace file to upload a file in place of a previous one. That means you could upload the new csv file each week. For that to work the Phantombuster output needs to either be a file, or a link to a publicly accessible file. If you’re not sure, could you share a screenshot of the data that you get from the Phantombuster trigger? Don’t forget to remove or obscure any personal or private information in any screenshot you share in the community (eg full name, email address, etc).

 

If you want to take some of the fields from the Phantombuster csv, or if the Google Sheet has different columns, etc, it’s still possible to do this, but it’ll take some more steps.

We need to use a combination of Google Sheets and Google Drive steps:

  1. New output from Phantombuster
  2. Google Sheets: Create Spreadsheet 
  3. Google Sheets: Create Spreadsheet Row(s)  - use the line items from the csv to add the rows to Google Sheets
  4. Google Drive: Find file - search for the file created in the previous step
  5. Google Drive: Replace file - Replace last week’s file with the one just created (in the field asking which file to upload, use the ‘File ODS’ field from the Find file step)

 

​I hope that helps, please let us know if you have any questions!

 

 

i didn’t know that google drive zap. That seems very promising. Will try that out and get back to you guys asap.

 

Really appreciate the help ;o)

 

Anders