I would like to use scripts for these two scenarios, because using formulas and/or conditional formatting can be altered when data is entered by "pulling" other nearby cells.

1. First Script

There are two Sheets: Sheet1 and Sheet2.

Sheet1 contains three columns:

Column A - a list of different ID's (ID1, ID2, ID3, ...),

Column B - filled with 0 or 1,

Column C - different names (Name1, Name2, Name3, ...).

Sheet2 contains two columns: Column A is 'ID' and Column B is 'Name'.

I would like a script that would autofill Sheet 2 in this way:

Column 'ID' - Show a list (column) of those ID's that have a value of "1" in Sheet1,

Column 'Name' - autofill the name associated with this ID in the first Sheet.

I know this can be done by using FILTER formula as well as INDEX, but as I said, I am trying to find a way not to use formulas in these sheets.

2. Second Script

Is there a way to conditional format some cells by using scripts? I would like a specific column (for example, Sheet1 - Column B) always to have a grey background (#EEEEEE), both when it is empty and when it is filled. This can be done by simply select this column and changing it's background, but I am looking for a way to make this possible by using Scripts.

Also, is it possible to color numbers 0 in red, and number 1 in green, if they are entered anywhere in some specific column, for example Sheet1, column D?

Thank you!

CodePudding user response：

Copy content:

Filter the source rows according to the number column, and then copy the filtered rows via setValues:

const number = 1;
const ID_COL = 1;
const NUM_COL = 2;
const NAME_COL = 3;

function autofill() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName("Sheet1");
  const sourceValues = sourceSheet.getRange(2,1,sourceSheet.getLastRow()-1,sourceSheet.getLastColumn())
                                  .getValues();
  const targetValues = sourceValues.filter(row => row[NUM_COL-1] == number)
                                   .map(row => [row[ID_COL-1], row[NAME_COL-1]]);
  targetValues.unshift(["ID", "Name"]);
  const targetSheet = ss.getSheetByName("Sheet2");
  targetSheet.clear();
  targetSheet.getRange(1, 1, targetValues.length, targetValues[0].length).setValues(targetValues);
}

You can rename your function to onEdit if you want data to be updated every time the sheet is edited (see onEdit).

Change background color:

Set the background color of your range via Range.setBackground, as shown below:

function setColumnBackground() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName("Sheet1");
  sourceSheet.getRange("B:B").setBackground("#EEEEEE");
}

Conditional formatting:

Use ConditionalFormatRule to manage conditional formatting rules, like this:

function addFormattingRules() {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName("Sheet1");
  const range = sourceSheet.getRange("B:B");
  const rule1 = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberEqualTo(0)
      .setBackground("red")
      .setRanges([range])
      .build();
  const rule2 = SpreadsheetApp.newConditionalFormatRule()
      .whenNumberEqualTo(1)
      .setBackground("green")
      .setRanges([range])
      .build();
  const rules = sourceSheet.getConditionalFormatRules();
  rules.push(rule1, rule2);
  sourceSheet.setConditionalFormatRules(rules);
}