Google sheet inventory with check out

Question

I have a very small stock where I want to make a checkout system.

I tried to figure it out if it's possible to make 2 different spreadsheets to work with each other in a complicated way. I have a spreadsheet where my initial data is in a Sheet "Data" where I have my main columns.

A item codes, B item name, D quantity

In a different spreadsheet I want to set up a very "easy" checkout sheet (because I can't have in the same spreadsheet, as includes sensitive data (prices, etc.). I want to make a dropdown list with 20 lines where the items can come up even by typing the product code or name: *C001 is linked for apple In the dropdown if I write c001 or apple to bring up the Apple as the item. In the next cell to each item the quantities to be checked out.

Somehow I can't manage to put together if it's 2 different spreadsheet. I could manage if they would be the same one , but in data validation can't use importrange with query or filter.

Can someone give me a shed of light if it's even possible to achieve something like that ?

Tried this code but somehow doesn't work:

    function checkout() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var checkoutSheet = ss.getSheetByName("Checkout");
  
  // Get selected item and quantity from checkout sheet
  var selectedItem = checkoutSheet.getRange("A2").getValue();
  var quantity = checkoutSheet.getRange("B2").getValue();

  // Open the Data spreadsheet
  var dataSS = SpreadsheetApp.openById("googlesheetID"); 
  var dataSheet = dataSS.getSheetByName("Data");

  // Find the row corresponding to the selected item in the data sheet
  var itemRow = getItemRow(selectedItem, dataSheet);

  if (itemRow !== -1) {
    // Update quantity in data sheet
    var currentQuantity = dataSheet.getRange(itemRow, 4).getValue();
    dataSheet.getRange(itemRow, 4).setValue(currentQuantity - quantity);

    
  } else {
    Browser.msgBox("Item not found in inventory.");
  }
}

function getItemRow(item, sheet) {
  var dataRange = sheet.getRange("B:B"); // Assuming Item Name is in column B
  var values = dataRange.getValues();
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] === item) {
      return i + 1; // Return the row number (adding 1 to match sheet indexing)
    }
  }
  return -1; // Return -1 if item not found
}

Answer 1

SUGGESTION

Based on the information provided, that being:

I want to make a dropdown list with 20 lines where the items can come up even by typing the product code or name...

What I can suggest instead is to use a dynamic code that updates either the provided item code or item name (from a given drop-down) to its corresponding value, basing off the information from the item database, as shown in the screenshot:

Installing the onEdit Trigger

To achieve this, we will first install the onEdit trigger on your spreadsheet, which can be achieved through these steps:

1. On your Apps Script editor, click the Triggers tab on the left menu (the clock/alarm icon)

2. At the bottom right part of the page, click "Add Trigger"

3. Select and configure the type of trigger you want to create, and then click Save.

Your setup should look like this:

The Script

I've made no significant changes to your provided script, and instead created a new function searchItem() specifically for the onEdit trigger; see the full script below:

function searchItem(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var checkoutSheet = ss.getSheetByName("SheetName"); 

  // Open the Data spreadsheet
  var dataSS = SpreadsheetApp.openById("googlesheetID").getSheetByName("Data"); 
  var dataVals = dataSS.getRange("A2:B").getValues().filter(x => x != ""); // removes all empty cells on dataVals

  var cell = ss.getActiveCell().getA1Notation(); // gets the A1 notation of the cell
  var item = checkoutSheet.getRange(cell).getValue();
  if (cell == "B2"){ // the value changed is on the Items dropdown list
    dataVals.forEach(x => x[1] == item ? checkoutSheet.getRange("A2").setValue(x[0]) : x);
  }
  else if (cell == "A2"){ // the value changed is on the Item Code column
    for(var i = 0; i < dataVals.length; i++){
      if (dataVals[i][0] == item){
        checkoutSheet.getRange("B2").setValue(dataVals[i][1]);
        break;
      }
      else if(i == dataVals.length - 1){
        // sets a blank value to denote that no item code was found in the database
        checkoutSheet.getRange("B2").setValue("");
      }
    }
  }
}

function checkout() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var checkoutSheet = ss.getSheetByName("SheetName");
  
  // Get selected item and quantity from checkout sheet
  var selectedItem = checkoutSheet.getRange("B2").getValue();
  var quantity = checkoutSheet.getRange("C2").getValue();

  // Open the Data spreadsheet
  var dataSS = SpreadsheetApp.openById("googlesheetID"); 
  var dataSheet = dataSS.getSheetByName("Data");

  // Find the row corresponding to the selected item in the data sheet
  var itemRow = getItemRow(selectedItem, dataSheet);

  if (itemRow !== -1) {
    // Update quantity in data sheet
    var currentQuantity = dataSheet.getRange(itemRow, 4).getValue();
    dataSheet.getRange(itemRow, 4).setValue(currentQuantity - quantity);
   
  } else {
    Browser.msgBox("Item not found in inventory.");
  }
}

function getItemRow(item, sheet) {
  var dataRange = sheet.getRange("B:B"); // Assuming Item Name is in column B
  var values = dataRange.getValues().filter(x => x != ""); // removes all empty cells on dataRange
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] === item) {
      return i + 1; // Return the row number (adding 1 to match sheet indexing)
    }
  }
  return -1; // Return -1 if item not found
}

What the function does is a 2-way updating functionality between the item code and item list:

• It first gets the updated data from the checkout sheet using the getActiveCell() and getA1Notation() functions, to determine if the data updated is the item code or the item name (from the drop-down list)

• The data is then compared onto the database, to see if the item code or item name is included

• If the item is found, it will return its corresponding item code on the checkout sheet

• Likewise, if the item code is found, the drop-down list will show the corresponding item assigned to that code; however, if the provided item code is not found on the database, a blank will be returned on the drop-down item list

And since the searchItem function is attached to the installable onEdit trigger, this means that it will automatically run whenever a change is made on the A2 cell (for the item code) or B2 cell (the item drop-down) and apply the necessary change accordingly.

OUTPUT

Fig.1 2-way updating functionality (data are included in the database)

Fig.2 Item Code is not present in the database

Fig.3 Full Implementation

UPDATE

Based on your last question, I've made a new function buildDataValidation() which builds a drop-down list based on the updated list of items from the Database sheet. See the function below:

function buildDataValidation(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var checkoutSheet = ss.getSheetByName("SheetName");

  // Open the Data spreadsheet
  var dataSS = SpreadsheetApp.openById("googlesheetID").getSheetByName("Data"); 
  var dataVals = dataSS.getRange("B2:B").getValues().filter(x => x != "").flat(); // removes all empty cells on dataVals
  var set_list = dataVals.filter(onlyUnique); // filters dataVals so that it only contains unique values from the data list

  // stores dataVals data into a Script Property
  var scriptProperties = PropertiesService.getScriptProperties(); 
  scriptProperties.setProperty('names', set_list.toString()); 
  var data = scriptProperties.getProperty('names');

  // creates the data validation
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(data.split(',')).build();
  ss.getRange("B2").setDataValidation(rule);
}

// serves as the filter to sort unique values
function onlyUnique(value, index, self) {
  return self.indexOf(value) === index;
}

The way the function works is that it will first get the list of items from the database sheet. It is then stored into a Script Property, which is a mini storage built in on the Apps Script service via accessing the PropertiesService method. From there, the data will be retrieved, and then assembled on the Checkout sheet with a new drop-down list.

Also, I've added an extra function that ensures that only unique values from your data list will be added onto the drop-down list, using the solution provided from this question.

And to ensure that the script runs automatically, we will also use the installable trigger for it, but instead will set to run the code every time the file is open, or using the onOpen installable trigger, as shown below:

OUTPUT

REFERENCES

• getActiveCell()

• getA1Notation()

• PropertiesService

• requireValueInList()