CODEWITHSUNDEEP
javascriptgoogle-apps-script
Tony
Tony

Reputation: 115

I'm looking to move cells with color highlighted to different sheet

I'm looking to copy the cells highlighted in particular color to the different sheet in one column.

Sample doc: https://docs.google.com/spreadsheets/d/1Xa_WKlmHO5mT688zM0O-LXlqnSbITG2YK1uLPq5XzcA/edit#gid=328987545

Can you help me with the script to get desired output?

Upvotes: 1

Views: 121

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your goal is as follows.

  • You want to retrieve the values of the highlighted cells (From your provided Spreadsheet, it's #ffff00), and want to put the values to the column "A" of destination sheet.
  • You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const srcSheetName = "Sheet1"; // This is from your sample Spreadsheet.
  const dstSheetName = "Ouput"; // This is from your sample Spreadsheet.
  const checkColor = "#ffff00"; // This is from your sample Spreadsheet.

  // Retrieve sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  // Retrieve source values and backgrounds.
  const srcRange = src.getDataRange();
  const backgrounds = srcRange.getBackgrounds();

  // Create an array for putting to the destination sheet.
  const values = srcRange.getValues().reduce((ar, r, i) => {
    r.forEach((c, j) => {
      if (backgrounds[i][j] == checkColor) {
        ar.push([c]);
      }
    });
    return ar;
  }, []);
  
  // Put the array to the column "A" of the destination sheet.
  dst.getRange(1, 1, values.length).setValues(values).setBackground(checkColor);
}
  • When this script is run, the values are retrieved from the highlighted cells of the source sheet. And, the retrieved values are put to the column "A" of the destination sheet.
  • From your sample Spreadsheet, the destination sheet has the highlighted cells. So, I added setBackground(checkColor). If you don't want to set the color, please remove it.

References:

Upvotes: 2

Related Questions