Can I modify one spreadsheet document (google sheets) based on information from a separate spreadsheet
Not sure if this is the best place to ask about spreadsheets, but here goes:
I'm trying to help someone modify information on one spreadsheet document when information on a separate spreadsheet document is modified (without coding a solution from scratch using the sheetsAPI).
For example, my client has a product database stored in a spreadsheet document, products, when a customer places an order on the clients website, a separate spreadsheet document, openOrders, is updated using the sheetsAPI and some server code. When this document is updated, I'd like to subtract the quantity of items being ordered, from the quantity of the item (or items) available being listed in products. I'm wondering if letting these spreadsheet documents "speak" to each other is possible without coding a solution server-side using the API.
products looks kind of like this:
Product Name | Units | Unit Price
---------------------------------
Product 1 | 6 | 1600
Product 2 | 3 | 1200
Product 3 | 17 | 400
Product 4 | 2 | 600
openOrders looks kind of like this:
Product Ordered | Units | Unit Price | Total
--------------------------------------------
Product 3 | 1 | 400 | 400
Product 2 | 2 | 1200 | 2400
Product 3 | 5 | 400 | 2000
Product 1 | 1 | 1600 | 1600
When the openOrders sheet is updated with a new order, I'd like to subtract the number of Units ordered, from the number of Units available in products. Keep in mind these are two separate spreadsheet documents. I am new to spreadsheets so this is all very foreign to me, it could be possible that I'm using the spreadsheets in a fundamentally incorrect way, and I'm open to that possibility.
you can do:
=ARRAYFORMULA(QUERY({A2:A, B2:B*-1;
IMPORTRANGE("ID_of_spreadsheet", "Sheet1!A2:B")},
"select sum(Col2) where Col1 <> '' group by Col1 label sum(Col2)''", 0)*-1)
