Using App Script to Replace Cell Color With Value-CodePudding

I have been using this Script since couple of month and its working fine but now the problem is i need to pull multiple column like Col, Col2, Col5, Col7, Col11 and so on. But this function is not working when i added a Multiple column then it stop working and throwing an error =SAMPLE(DATA!S3:S&DATA!M3:M,"DATA!S3:S&DATA!M3:M",D3:D)

I would appreciate your help in this regard.

function SAMPLE(srcValues, srcRange, dstValues) {
const srcBackgrounds = SpreadsheetApp.getActiveSpreadsheet().getRange(srcRange).getBackgrounds();
return srcValues.map((r, i) => r.map((c, j) => {
if (dstValues[i][0] != "") {
if (srcBackgrounds[i][j] == "#f4cccc") {
return "MISSING";
}
if (srcBackgrounds[i][j] == "#b7e1cd") {
return "COMPLETE";
}
return c == "P" || c == "p" ? "Pending" : "";
}
return "";
}));
}

CodePudding user response：

In your situation, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet. When you use this script, please put a custom function of =SAMPLE2("Data",Data!A2:A,"24,2,11,4,18,6",2). By this, the result values are returned. In this case, from your sample Spreadsheet, "24,2,11,4,18,6" means Col23 Col1 Col10 Col3 Col17 Col6. Because the 1st column is ID in your Spreadsheet. And, the last argument of 2 means the 1st row. In your situation, the 1st row of the data is row 2. So, 2 is used. Please be careful about this.

function SAMPLE2(sheetName, dstValues, columns, startRow) {
  columns = columns.split(",").map(e => Number(e.trim()));
  const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange();
  const srcBackgrounds = range.getBackgrounds();
  const values = range.getValues();
  if (startRow > 1) {
    srcBackgrounds.splice(0, startRow - 1);
    values.splice(0, startRow - 1);
  }
  return values.map((r, i) => columns.map(col => {
    const j = col - 1;
    const c = r[j];
    if (dstValues[i][0] != "") {
      if (srcBackgrounds[i][j] == "#f4cccc") {
        return "MISSING";
      }
      if (srcBackgrounds[i][j] == "#b7e1cd") {
        return "COMPLETE";
      }
      return c == "P" || c == "p" ? "Pending" : "";
    }
    return "";
  }));
}

In this modification, in order to use the specific columns, all data range is used. So, sheetName is used.

Reference:

map()

Added:

As another approach, how about the following sample script? In this sample sript, please put a custom function like =SAMPLE3("Data","Data!A2:A","24,2,11,4,18,6"). By using "Data!A2:A" as the string value, the 1st row is retrieved from it.

Sample script:

function SAMPLE3(sheetName, dstRange, columns) {
  columns = columns.split(",").map(e => Number(e.trim()));
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dstRangeObj = ss.getRange(dstRange);
  const dstValues = dstRangeObj.getValues();
  const srcRange = ss.getSheetByName(sheetName).getDataRange();
  const srcBackgrounds = srcRange.getBackgrounds();
  const values = srcRange.getValues();
  const startRow = dstRangeObj.getRow();
  if (startRow > 1) {
    srcBackgrounds.splice(0, startRow - 1);
    values.splice(0, startRow - 1);
  }
  return values.map((r, i) => columns.map(col => {
    const j = col - 1;
    const c = r[j];
    if (dstValues[i][0] != "") {
      if (srcBackgrounds[i][j] == "#f4cccc") {
        return "MISSING";
      }
      if (srcBackgrounds[i][j] == "#b7e1cd") {
        return "COMPLETE";
      }
      return c == "P" || c == "p" ? "Pending" : "";
    }
    return "";
  }));
}