How to automate updates for Airtable codes and colors with Zapier

Hi! If I understand well, the uploaded spreadsheet will contain the colour codes for the day (tell my if I unerstood wrong). Could we get a screenshot of this spreadsheet? Also please describe the Airtable page where the codes & colors will be stored? What would the columns be?

OK, so the spreadsheet has a column for colour codes and a column for the codes. There are also other columns but they dont really matter, besides the colour column and code column. I have attached a screenshot of the airtable.

Ok my advice then would be to make sure your .csv is uploaded or converted into Google Sheets format. Set the trigger to “New file in folder” to get it. Then using the file ID run a “Get many spreadsheet rows” action in Google Sheets. This sould return all of the file’s lines under line-item format.

You can then map these captured lines to Airtable, either with the “Create Records (Advanced)” action if it’s less than 10 lines, or otherwise using a Looping step to creates the lines one by one.

Is this going to update lines 1 by 1? Because there will be a new spreadsheet everyday, and all data inside the columns will be different. 

This is what I have for the Google sheets action, but I’m pretty lost on what next to do. Would you mind guiding me on what to do next?

Sure! Then you could open an Airtable action "Create Records" in Airtable, and map the results of your GSheets step.

This will only create new lines in Airtable. But tell me if you'd prefer to delete all previous lines before, or even update these existing lines 

Yeah could I update all lines? Also, I don’t know how to finish the Gsheets action. 

Hi ​@Timmycoder,

Yes, you can definitely update all lines in a Google Sheets document using Zapier.

Hi again! To finish this here are the main points of what I would recommend to do:

• Get the rows of your newly imported spreadsheet (Done)
• Run the Airtable “Find Many Records” action, and set it so it just catches all of them (put a broad lookup value)
• Open a Looping step, choose “Loop from Line-items”, and map all the fields that you’ll need. Say you just need the AT Records IDs and the new Color codes (from GSheets). Write a clear field name at the left and map the dynamic values from previous steps at the right. These should be lists of values
• Test the Looping and it should return an example value for both “AT Records IDs” and “Color Codes”
• Use these values to map in a “Update Record” Airtable step. Make sure to use the output values of the Looping step, not the previous lists

Hopefully this will respond to your needs, let me know if I can make myself clearer :)

Would it be possible for you to show me in pictures, because there are quite a few things, that I dont know if I should fill in and what to fill in. Thanks

Hey ​@Timmycoder, just checking in—were you able to get the Zap set up in the end? 

If you’re still unsure about what to fill in, feel free to share some screenshots of any areas you’re getting stuck on (just make sure to blur or remove any personal details). That way, we can help guide you through it!

Hi, unfortunately no. There are many fields which I don’t know what to input, and im getting really overwhelmed trying to figure out how to link the airtable properly. If you wouldnt mind helping me? I can send the screenshots in afterschool.

So far, I have set up a trigger, that is, When a file has been added into a google drive, something happens. The action is supposed to Delete all the information from the original airtable/google sheet, and go into the file that has been inputed into the google drive and move that information into the airtable. I’m doing this, instead of switching the data set entirely, is because the app, that I made on an app maker doesn’t allow for me to switch the sheet, without updating, and physically going into the app maker to change the data stuff, and that isnt viable for me, because the app will be in use for 4 years every single day. The attachements that have Q_ are the ones I’m confused about. 

Happy to lend a hand, ​@Timmycoder! 🙂 

Thanks for sharing those screenshots. It seems that you’ve set up the Get Many Spreadsheet Rows action correctly to pull the data from the file that get’s uploaded to the Google Sheets spreadsheet—nice work! 🙌

So next, if you’re wanting to overwrite the existing records in Airtable with the information that’s in the spreadsheet you’ll need to use a Looping action that contains a Find Record action followed by a Update Record action. This will allow the Zap to run the Airtable actions once for each of the rows of information that the Google Sheets action outputs:

In the Looping action you’d want to add any values that you want to pass from Google Sheets to Airtable. For example if you were wanting to pass just the codes and colours you’d select the relevant fields that contain those values:

Then you’d select the code value from the Looping action in the Find Record, as that will ensure it looks at the Code field in Airtable to find a record with that same code. For example:

Then in the Update Record action, in the Record field you’d chose the Custom value option and select the ID field that’s output by the Find Record action:

And select any other values from the looping action that you want to update on the record in Airtable. For example: 

Please note: when testing in the Zap editor only the first loop’s data will be used but when the Zap is turned on runs live, all the loops will be run (and appear as separate Zap runs) so all the rows that were found by the Google Sheets action will be searched for in Airtable and have the colour updated. You can learn more about working with loops here: Loop your Zap actions.

Hope that helps to get you pointed in the right direction. If you run into any issues on that or have any questions, just let me know!