CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Shun Zu
Shun Zu

Reputation: 119

How to convert the apps script's onEdit function into a regular function?

I am using the following onEdit function to fetch color from cells and apply it to Main Sheet's column B values that match the content of the colored cell in another sheet (Settings). How can I convert this into a regular function? So, I can call it to go through all the rows/ cells of column B to apply the respective color at once.

Also, I want to specify the Main Sheet's column B start and end point (row) in the Settings Sheet's cell F1 and G1. For example: Apply colors to row 4 to 50 of the Main Sheet only and then terminate.

F G
4 50

Please view the attached sheet for reference: https://docs.google.com/spreadsheets/d/1UmHU76ZOoHhB-8HyHrY2ESZAjUpTq2FKkGFmGXNEQ2o/edit?usp=sharing

Code provided by @Tanaike

function onEdit(e){
if(e.source.getActiveSheet().getName() == "MAIN"){

  const {range, source, value} = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "MAIN" || range.columnStart != 2 || range.rowStart == 2) return;
  const settingSheet = source.getSheetByName("SETTINGS")
  const r = settingSheet.getRange("B5:C11" + settingSheet.getLastRow());
  const colors = r.getBackgrounds();
  const obj = r.getValues().reduce((o, [b], i) => (o[b] = colors[i][1], o), {});
  range.setBackground(obj[value] || null);
  }
}

Upvotes: 1

Views: 55

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • You want to convert your script without using onEdit. And, you want to retrieve the values from "F1:G1" of "SETTINGS" sheet as the start and end rows of the column "B" of "MAIN" sheet.

In this case, how about the following modification?

Modified script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [main, settingSheet] = ["MAIN", "SETTINGS"].map(s => ss.getSheetByName(s));
  const [start, end] = settingSheet.getRange("F1:G1").getValues()[0];
  const range = main.getRange(`B${start}:B${end}`);
  const r = settingSheet.getRange("B5:C" + settingSheet.getLastRow());
  const colors = r.getBackgrounds();
  const obj = r.getValues().reduce((o, [b], i) => (o[b] = colors[i][1], o), {});
  const bk = range.getValues().map(([value]) => [obj[value] || null]);
  range.setBackgrounds(bk);
}

Upvotes: 1

Related Questions