Is there a way to detect clicks in google sheets and run a function?

Question

I have a Google Spreadsheet as shown below.

The 30 at the bottom is what the title takes to display the points. How do I make it so that when the user clicks the buy button it will deduct the amount of points next to it (ignoring the text) from the 30 at the bottom and add a line below the 30 with it saying the item that was just purchased?

EX: User clicks the "Buy" Button on Item 1. Code takes the numbers only from the previous cell and deducts it from the 30 at the bottom of the sheet then creates a new row under the cell that was 30 and the new line's first cell displays the "Item" Reference Image:

If anyone could help me with this that would be great... Thanks! Update: I have it working now, But how can I make it so that instead of deleting the previous purchases it adds it to an unused line? Here is the code so far:

function onSelectionChange(e) {
 const sheet = SpreadsheetApp.getActiveSheet();
  const resultA1Notation = "A21"; // Change according to your preferences
  const itemNotation = "A22"; // Change according to your preferences
  const range = e.range;
  const selectedValue = range.getValue();
  if (selectedValue === "Buy") {
    let numberValue = range.offset(0, -1).getValue();
    const itemValue = range.offset(0, -4).getValue();
    if (numberValue !== "Free") {
      numberValue = numberValue.slice(0, -1); // Remove P
      const resultCell = sheet.getRange(resultA1Notation);
      resultCell.setValue(resultCell.getValue() - numberValue);
    }
    sheet.getRange(itemNotation).setValue(itemValue + " was purchased!");
  }
}

Thanks!

Answer 1

You can use a simple onSelectionChange(e) trigger, which will fire your function every time a user selects a different cell/range in the spreadsheet. You can do the following:

• Check if the selected value is Buy.
• If that's the case, retrieve the number value on its left, and the item name next to it, using Range.offset(rowOffset, columnOffset).
• Check if the value is Free. If that's not the case it is assumed that it is a number and the letter P. Remove this last character using slice and update the result cell at the bottom using getValue and setValue.
• Update the cell below the result number, telling about the item that was purchased, using setValue again.

Code snippet:

function onSelectionChange(e) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const resultA1Notation = "A11"; // Change according to your preferences
  const itemNotation = "A12"; // Change according to your preferences
  const range = e.range;
  const selectedValue = range.getValue();
  if (selectedValue === "Buy") {
    let numberValue = range.offset(0, -1).getValue();
    const itemValue = range.offset(0, -2).getValue();
    if (numberValue !== "Free") {
      numberValue = numberValue.slice(0, -1); // Remove P
      const resultCell = sheet.getRange(resultA1Notation);
      resultCell.setValue(resultCell.getValue() - numberValue);
    }
    sheet.getRange(itemNotation).setValue(itemValue + " was purchased!");
  }
}

Note:

• I have assumed that the result at the bottom and the message showing which item was purchased are located in cells A11 and A12. Please change these notations in the code snippet if that's not the case.
• Event object for onSelectionChange is currently not documented, but its properties can be known through some little testing (see Cooper's comment showing all the object properties). Specifically, range is a property of this object and it is used by the code snippet above.