CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Eric
Eric

Reputation: 503

Cannot figure out how to change selected cell in Google Apps Script

I have the following script:

function onSelectionChange(e) {
 const sheet = SpreadsheetApp.getActiveSheet();
  const resultA1Notation = "D2"; // Change
  const range = e.range;
  const selectedValue = range.getValue();
  if (selectedValue === "UPGRADE") {
    let numberValue = range.offset(0, -1).getValue();
    if (numberValue !== "Free") {
      numberValue = numberValue.slice(0, -1); // Remove P
      const resultCell = sheet.getRange(resultA1Notation);
      resultCell.setValue(resultCell.getValue() - numberValue);
      sheet.range.offset(0, -1).getValue();
      resultCell.setValue(resultCell.getValue() + 15);
    }
  }
}

I can't get it to work through. I'm trying to modify these two lines:

sheet.range.offset(0, -1).getValue();
resultCell.setValue(resultCell.getValue() + 15);

I'm trying to get the script to offset the cell by -1, ex if it's B1 it would go to A1. Any help it appreciated, thanks!

Upvotes: 0

Views: 995

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to activate one left side cell for the current cell, when the selected cell value is UPGRADE and the value of one left side cell for the current selected cell is not Free.

    • As a sample situation, when the cell "B1" is selected, you want to select the cell "A1".

  • You want to modify the following part in your script.

      sheet.range.offset(0, -1).getValue();
  resultCell.setValue(resultCell.getValue() + 15);

In this case, in order to select the cell, I think that activate method can be used. When your script is modified, it becomes as follows.

From:

sheet.range.offset(0, -1).getValue();
resultCell.setValue(resultCell.getValue() + 15);

To:

if (range.columnStart > 1) {
  range.offset(0, -1).activate();
}
  • When the column "A" is selected, range.offset(0, -1) occurs an error. So I used the if statement of if (range.columnStart > 1){}.

Reference:

Upvotes: 2

Related Questions