Best answer

Updating a spreadsheet on Google Sheets from supplier wholesale price list

  • 17 March 2020
  • 3 replies
  • 591 views

Hi All,

Looking for help on how to update a in-house order spreadsheet on Google sheets, from supplier price list.

The inhouse order spreadsheet is used to calculate the total cost of the orders before the order is placed with the supplier.

Every time a supplier has a price change the internal in-house order spreadsheet has to be edited to reflect this.  This takes a very long time as some sheets have 600+ rows.  Also the manual process collects lots of errors from human data entry.  Having a app to do this would be fantastic!

The in-house order spreadsheet has:

  • been edited from supplier spreadsheet
    • removing unstocked items and removing unnessary columns of unrequired data
    • adds additional columns for item’s unique data for max and min shelf quantity, and notes
  • order spreadsheet retains original supplier sku

The ideal process to update inhouse order spreadsheet:

  1. Download the supplier price list as .xlsx (or .pdf which would be converted to .xlsx either by online service or Zap app)
  2. Edit supplier price list to:
    1. delete unstocked items as in deleting rows
    2. delete unrequired columns
  3. Use Zap application to export price and sku data from edited supplier price list and push into in-house order sheet to copy over prices for matching item sku
  4. Process preserves the existing in-house data for max and min stock levels and notes

Hope this is possible with Zapier apps!

look forward to how I could proceed with this,

thanks in advance

Russell

icon

Best answer by Danvers 18 March 2020, 14:07

View original

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

3 replies

Userlevel 7
Badge +10

Hi @RussellDavie 

I think you’re going to find Zapier isn’t the tool for this.

  1. There is no easy way to download/read an XLS sheet, grab all rows and action on them at once.
  2. Zapier can’t delete rows on a sheet - or it can, but it leaves them blank (which also has the affect of not letting Zapier ‘see’ any rows below a blank row)
  3. Zapier can’t delete column (and if you delete columns manually, it will mess up your zap mappings).

Hi @AndrewJDavison_Luhhu 

thanks for your reply!

The editing of the spreadsheets to get them into shape is done by another process, typically manually.  I don’t expect Zapier to do that.

What I a wanting to know is if Zapier could export price data associated with the sku in the wholesale price list and push that into the price field of the same sku in the order spreadsheet. 

thanks again!

 

 

 

Userlevel 7
Badge +12

Hi @RusellDavie! If you what you want to do is 1. Export the data from the supplier into a sheet 2. check the price of products (using SKU) and then update those on your sheet then yes, you can do that. 

Your Zap would look like this:

  1. Trigger: Google Sheets New Spreadsheet Row
  2. Action: Google Sheets Lookup Spreadsheet Row (search your in-house based on SKU)
  3. Action: Google Sheets  Update Spreadsheet Row (update the row found in the previous step)

You’ll need a new Google Sheet that you’ll use to trigger the Zap. When you export the data from the supplier, copy and paste the information as new rows in that sheet. 

 

One thing to point out is that the columns for the both sheets will need to stay the same for the Zap to work. That is, they don’t have to be the same as each other, but you can’t add or remove columns from either sheet without likely breaking the Zap. 

 

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