Update Summary Colors

Using Google Apps Script for the summary colors and to pull the data based on cell background colors (green and red) and appends them into the respective summary sheets also I added a time-driven trigger to allow you to schedule the script to run automatically at a specified time each day.

Script used

function myFunction() {
  const base = "Base data";
  const green = "Summary_Green";
  const red = "Summary_Red";
  const greenColor = "#00ff00";
  const redColor = "#ff0000";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const baseSheet = ss.getSheetByName(base);
  const greenSheet = ss.getSheetByName(green);
  const redSheet = ss.getSheetByName(red);

  greenSheet.clearContents();
  redSheet.clearContents();

  const dataRange = baseSheet.getDataRange();
  const values = dataRange.getValues();
  const backgrounds = dataRange.getBackgrounds();

  greenSheet.appendRow(["Date", "SKU Name", "Quantity"]);
  redSheet.appendRow(["Date", "SKU Name", "Quantity"]);

  for (let i = 1; i < values.length; i++) {
    for (let j = 3; j < values[0].length; j++) {
      const cellValue = values[i][j];
      const cellColor = backgrounds[i][j];

      if (cellColor === greenColor) {
        greenSheet.appendRow([values[0][j], values[i][2], cellValue]);
      } else if (cellColor === redColor) {
        redSheet.appendRow([values[0][j], values[i][2], cellValue]);
      }
    }
  }
}

function createDailyTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  // Delete existing time-based triggers to avoid duplicates
  for (let i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "myFunction") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  // This is for a new  daily trigger
  ScriptApp.newTrigger("myFuction")
    .timeBased()
    .everyDays(1)
    .atHour() // Set to run daily Example 9 AM should be (9)
    .create();
}

Sample Result

Base Data

Summary_Green

Summary_Red

References

• AppendRow
• Manage triggers programmatically