I am being asked to make a Google spreadsheet do the following and I have no idea how to make this happen:

I work at a car dealership where we track live sales on a shared Google spreadsheet. They go on the sheet when the deal starts and get removed (deleted) when the sale is finalized. Each line item (deal) has a drop down cell (column G) with different deal statuses. One of the statuses is "Missing Docs" meaning the sales team did not provide all documents necessary to generate the contracts. We want to track who runs high rates of missing docs, so we're thinking that when "Missing Docs" is selected on a row, the script will automatically run and snapshot the Customer Name (column A), Stock Number (column B), Sales Person Name (column D) and Issue Notes (column I), then drop that data into another worksheet to be saved a historic data record.

Any help building a script that would perform this function would be greatly appreciated!!

CodePudding user response：

Try this:

function getMissingDocs() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SheetName");
  const osh = ss.getSheetByName("outputSheetName");
  const vs = sh.getDataRange().getValues().filter(r => r[6] == "Missing Docs").map(r => [r[0],r[1],r[3],r[8]]);
  Logger.log(JSON.stringify(vs));
  osh.clearContents();
  osh.getRange(1,1, vs.length, vs[0].length).setValues(vs);
}

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet Name" && e.range.columnStart == 7 && e.value == "Missing Docs") {
    getMissingDocs();
  }
}

If you have other onEdit functions you will need to integrate them together into one function or another option is to rename it and use an installable onEdit function.