How to reduce Inventory in sheet1("stock") on the bases of product sold in sheet2("sales") through google sheets script...?

Question

In Google Sheets for inventory management, sheet1 is stock in which Product and Quantity are mentioned, sheet2 is the sale of the day. I want to automatically deduct stock when a product is written over there, if apple is sold, then it should automatically deduct one quantity from sheet1. The sheet is here.

Some code like this:

function onedit(sheet2,A2) {
    if the product in Sheet2!A2 is in Sheet1!A2:A,Subtract 1 from Sheet1!B2:B
}

Answer 1

In your cell B2, you can insert this formula:

= 4 - SUM(QUERY(sold!$A$1:B, 
 "select count(A) 
  where A = '" & A2 & "' 
  group by A", false))

Then, you can drag it down to let the formula apply to all your rows.

The formula counts the number of occurrences in the "sold" sheet of the term in the column A of the first sheet. The QUERY function returns a table with one column and two rows (header and data). the SUM is applied to it, and reduces the QUERY result to a single number.

As with all formulas, results are kept up to date by the Google sheet automatically.

Instead of having the initial inventory in the formula itself, I suggest to use a separate column, for example column C. The formula above would then start with C2 instead of 4.