Update CMS items from a public CSV doc. Is Zapier the answer?

Design help Data API
1

Hi There,

I have a table that contains a list of companies and has columns with different data relating to each company. I want this table to auto update. Each day the new data I need the table to have is published on a publicly available csv.

The company names on this list don’t change, ie no companies are added or removed from day to day.

What about using Zapier to update the Webflow CMS collection?

In Zapier I can schedule an import of that day’s CSV using a Zap with these actions:

  1. Trigger set to fire daily at set time
  2. Date/Time to format the current date to insert into the URL of that day’s CSV.
  3. Format>Utilities>import CSV file from the URL

But then, how am I going to use this to update the items in the CMS collection?

I can’t create a step 4 Webflow>Update Live Item because the required ‘Item’ field requires the item ID.

I can get the IDs into a Google Sheet by using Made by Marius’ two tab /2 Zap solution here

Ie. I would add a step 4 to above GSheets>Create New Spreadsheet Row(s). Then another Zap triggers on GSheets > New or Updated Spreadsheet Row, has action 2 as Webflow>Create Live Item, and action 3 of Create Spreadsheet Row in a second tab on the GSheet.

Now, then after doing the process once I would have the company list on the first tab without IDs and on the second tab with a column of item IDs, and (if I’d deleted the CMS collection items before) then I would have a matching set companies in my CMS Collection.

But now what?

I can’t repeat this the next day as the Create New Spreadsheet Row(s) step at the end of the first Zap means I get all the companies listed twice on both the GSheets table and in the CMS collection. I only want to have the same companies once in the CMS collection.

Apart from the thread above, Ive also read with interest these other conversations on the forum including this one

but I can’t figure out how I can apply this in my case. Could anyone help me out?

Many thanks!

Here is my site Read-Only: https://preview.webflow.com/preview/demz?utm_medium=preview_link&utm_source=designer&utm_content=demz&preview=d6798950acbbe51de07252cf561a303e&workflow=preview
(how to share your site Read-Only link)

2

I haven’t used Google Sheet much for Zapier routines. What about Airtable? Haven’t tried it but I’d do the steps below but I remember Zapier didn’t have a proper loop function so you’d have to use the Code by Zapier and write that loop in Javascript (I found a solution once in Zapier’s forum).

  1. Trigger set to fire daily at set time
  2. Date/Time to format the current date to insert into the URL of that day’s CSV.
  3. Format>Utilities>import CSV file from the URL
  4. Find Record in Airtable (using Company Name?)
  5. Update Record in Airtable (all the fields from CSV)

Path A ( if there is no Live Item ID in Airtable )
A1. Create Webflow Live Item (all fields)
A2. Update Record in Airtable (Add Live Item ID from A1)

Path B ( Else )
B2. Update Webflow Live Item (all fields)

Now I think this would be a lot easier to do with Integromat but if you are not familiar with it, it should take you a week or 2 to get started.

Sorry if this was not much help! Good luck!

[edit] Apparently Zapier has added a loop helper

3

Hiya. And thanks! Very helpful. I will investigate further.

4

Hiya Ian - I wondered if you’d mind explaining this to me a little more. I’m new to Airtable, but I get that with Zapier you can Find a record and Update it. What would be my next step after this? sorry !

5

The idea here is that you import your CSV to a table format and assign the Webflow Live Item ID first time you run it, so after that everytime it imports the CSV it knows which one is which in Webflow.

Btw I just checked and Google Sheets has apparently what you need with “Lookup Spreadsheet Row in Google Sheets”.

Do you mind sharing a screenshot of your setup in Zapier and also your an example of your CSV?

6

Thanks so much for the explanation.

First I ran a Zap to populate the lookup table and CMS collection:

  1. Schedule by Zapier: Every Day
  2. Formatter by Zapier: Utilities
  3. Looping by Zapier: Create Loop From Line Items
  4. Webflow: Create Live Item
  5. Google Sheets: Create Spreadsheet Row.

then turned that off and will run this which triggers daily:

  1. Schedule by Zapier:Every Day
  2. Formatter by Zapier: Date / Time
  3. Formatter by Zapier: Utilities
  4. Looping by Zapier: Create Loop From Line Items
  5. Google Sheets: Lookup Spreadsheet Row
  6. Webflow: Update Live Item

This should work to update all the companies info.

Now I need to figure out what to do in the rare occurence that the list of companies changes. Some way of adding and taking away companies from the list - or at the miniumum, some set-up that would cause a Zap error so i would be alerted to make the changes manually.

7

For adding companies you could merge these two into one, and use Path (If Condition) to split paths for when it’s a new entry (company) vs an update.

As for removing, if the omission of a company from the CSV is an indication that the company has been removed, you can add a new column to you Google Sheets (and maybe also Webflow collection) with for example Last Updated, then check if it’s older than today to delete (archive, draft). Alternatively you could just use Condition inside Webflow Collection to filter those outdated:

Screen Shot 2021-06-10 at 11.43.56 AM

1 Like
8

Awesome ! I will try this out