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

Question

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); } }

Tanaike · Accepted Answer · 2022-02-06 12:47:15Z

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); }

@Shun Zu Thank you for replying. I'm glad your issue was resolved.

Collectives™ on Stack Overflow

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

1 Answer 1

Modified script:

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

2 Comments

Linked

Related