1

I have created a Google Sheet to store, edit and upload items I am selling on eBay. Originally I thought of using a calculator to calculate a selling price (Column O Sheet1).

I found this link on Stack Overflow that I am wondering if it could be modified to perform this task. Their code creates a trigger to add new data once a new row is added. However my Spreadsheet does not add new rows, it automatically updates at certain times of the day. My formula would be something like this =SUM(Sheet1,ColO2:O + $3.00Fee) + (15% Markup) *14%.

Can someone please help me modify the above trigger to work.

Improve this question
5
  • Hi, welcome to WebApps. FWIW: you made the right decision to junk the pricing form. You said my Spreadsheet does not add new rows, it automatically updates at certain times of the day. Would you please explain this automatic update process; is this how new rows are added?; are any existing rows deleted in this process? are any existing rows amended in this process? Would you also be more specific about what "certain times of the day" means? Is there any indication of which (if any) rows have been added? which sheet are new rows added to?
    – Tedinoz
    Jul 21, 2022 at 2:01
  • Hi Tedinoz, my data comes from ftp through a setup i got done in 'Talend'. After the initial import, 2 columns gets updated i think every 4hours. Price and quantity columns. However the price is what the item cost me so i need to add a mark up and fees etc.
    – user19353313
    Jul 22, 2022 at 3:07
  • You've asked for help getting "the above trigger to work" but I respectfully suggest that this is NOT a description of your problem. I think that your problem has several aspects including 1) how to identify when a new row is added, 2) how to identify when an existing "price" field is changed, 3) how to structure a "pricing sheet" for the calculation of product prices, 4) how to update the "Selling price" field for a given product ONLY when you have satisfactorily supplied all the calculation parameters, and so on... Would you care to comment.
    – Tedinoz
    Jul 22, 2022 at 7:10
  • Hi, I would have to agree with you.
    – user19353313
    Jul 23, 2022 at 23:24
  • OK, that helps with planning. We can't solve everything in just one question but there are three essentials. 1)develop a pricing method. I've submitted an answer that gets the ball rolling. 2) establish whether addition of new products can be detected (maybe an installable onChange or onedit) and 3) ditto for updates for Price/Quantity fields? You need to work on these latter tasks because of the unique spreadsheet update method. Any problems that you have should be raised as a new question. Thoughts?
    – Tedinoz
    Jul 25, 2022 at 8:12

1 Answer 1

Reset to default
1

This is a suggestion for a solution to the selling price calculation method. Note this method doesn't include a process to "trigger" and update to the "Data 1" sheet. This method uses a query but possibly script is an alternative to create a copy of "Data 1" on every change.

  • a new sheet should be created, let's call the sheet "Pricing".

  • Cell A2 contains a query to return the Stock code and the List price

    • =query({'Data 1'!B1:B,'Data 1'!O1:O})

  • Columns C, D, E, F & G are provisions for additional costs

  • Cell H3 = =arrayformula(if(len(A3:A)>0,B3:B+C3:C+E3:E+G3:G,))

  • adds values from Columns B, B, E and G

  • Column I contains the three Data validation options for Margin/Return

  • Column J contains the integer value of the respective profit adjustment

  • Cell K3 = profit formula

    • =arrayformula(iferror(if(I3:I="Net profit($)",fixed(round((H3:H+J3:J), 2),2),if(I3:I="Margin(%)",fixed(ROUND(H3:H/(1-(J3:J/100)), 2),2),if(I3:I="Return (%)",0.99,))),ISERROR(1/0)))
    • Note, the meaning of "Return (%)" is unclear and you should substitute your own formula there

  • Column L contains the various data validation options for eBay rates

  • Column M contains the ebay cost

    • Cell M3: =ARRAYFORMULA(IF(ISBLANK(L3:L), ,ROUND((L3:L*K3:K), 2)))

  • Column N = Total Selling price

    • Cell N3: =arrayformula(if(len(A3:A)>0,K3:K+M3:M,))

  • Column O = "Update to data"

  • (Untested): A checkbox that, when ticked, flags that the sell price calculation FOR THAT PRODUCT is complete and the price can be picked up in the "Data1" sheet. The method for executing this is not finalised; at its simplest it might be a query or vlookup formula in a Sell price" column in Data, or an onEdit script that physically writes the price to the respective Sell price field. It's not resolved yet what means would conform that the sell price has been updated (as opposed "can be" updated.

  • There are many ways that the checkboxes could be created - depending on how the data on "Pricing" is initiated". One method is as follows:

    • Inserting a header in cell O2.
    • Highlight the range O3 to bottom of the column
    • Select "Insert", "CheckBox"
    • Select the entire Column O,
      • Select "Conditional Formatting", Custom Formula
      • Enter =ISBLANK($A3)
      • Edit the background colour AND the text colour to be white.

  • The effect is that when there is a product code in Column A, a blank checkbox will "appear" in Column O (h/t: @MichaelW-How to auto add/remove checkbox's whether different cells contain value?

Sample

pricing sample

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.