Input box opens when when cell changed to "complete"-CodePudding

I have a spreadsheet to track jobs. When staff mark a job as complete they are meant to fill in the total amount charged to the customer. However about 50% of the time they forget

Can I create a script that when they change the "status Column" to "Complete" an input box opens for them to fill in the $ amount and records it when they click ok in a corresponding column in the same row?

CodePudding user response：

try this script

SHEET_TO_WORK_ON = 'Sheet6'; // Please enter the name of your sheet here
STATUS_COLUMN_NUMBER = 3;    // Enter the number of the column with the statuses here
CHARGED_COLUMN_NUMBER = 4;   // Enter the number of the column with the total amount charged here

function onEdit(e){
  let range = e.range,
      sheet = range.getSheet();

  if (sheet.getName() == SHEET_TO_WORK_ON && range.columnStart == STATUS_COLUMN_NUMBER && range.rowStart > 1 && e.value == 'complete'){
    range.offset(0,CHARGED_COLUMN_NUMBER - STATUS_COLUMN_NUMBER).setValue(Browser.inputBox('Charged ', 'fill in the $ amount', Browser.Buttons.OK))
  }
}