I have a Google Sheets spreadsheet containing product inventory. There are multiple sheets within the document, each sheet containing products of a particular brand. The two important columns in each sheet that I'd like to be able to work with are Part Number, and Quantity.
What I want to be able to do in my web app is, given a particular part number and brand name, look up the corresponding quantity in the correct sheet in my Google Sheets document, and send that value back to to the web app to display. (Essentially, I want the web app user to be able to click on a part number, and get the current quantity of that item displayed next to it.)
I've tried publishing the Google Sheets document to the web, and accessing it in PHP as several of the formats Google offers (csv, xlsx, and ods) and have hit a wall in each instance:
- CSV only lets me access the first sheet in the document
- XLSX isn't readable by the PHP class I tried using (excel_reader.php, which I use for other things and is otherwise perfect but I guess can only read XLS, not XLSX)
- ODS has its own PHP class as well, but it seems overly complex and unwieldy for what I'm trying to do
I don't need to modify the Google Sheet in any way with this... all I need to do is go, "Given brand X and part number Y, return the value in the Quantity column of this Google Sheet." I'm really hoping there's a (fairly) simple way to do this...