I have a function that change the background color of cells. Function works well and do the thing I want, but I met one problem that I don't really know how to solve.
I want this function to loop through all used columns in spreadsheet. (for now it is from G till TP column will increase)
As you can see the function I have now do the thing only with G column. How to make it loop till the last used column?
function insertColor2() {
const sheetName = "結果１"; // set the sheet name.
// 1. Retrieve values from sheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const values1 = sheet.getRange(3, 7, sheet.getLastRow() - 2,1).getValues();
const values = [];
values.push(values1);
// 2. Create an array for modifying the background colors.
const backgroundColors = values.map(([,,c,,,...g]) =>
g.reduce((o, e) => {
if (e.toString() != "") {
o.total += e;
o.colors.push(c >= o.total ? null : "red");
} else {
o.colors.push(null);
}
return o;
}, {colors: [], total: 0}).colors
);
const flatten = [].concat.apply([], backgroundColors);
const newArr = [];
while(flatten.length) newArr.push(flatten.splice(0,1));
Logger.log(newArr);
// 3. Modify the background colors of cells.
sheet.getRange(8, 7, newArr.length, 1).setBackgrounds(newArr);
}
So I found a solution to solve this problem. It is not the best solution but works well. I just simply transposed array twice at the beginning and before inputting result to the sheet.
function transpose(a) {
return Object.keys(a[0]).map(function(c) {
return a.map(function(r) { return r[c]; });
});
}
function transpose1(original) {
var copy = [];
for (var i = 0; i < original.length; ++i) {
for (var j = 0; j < original[i].length; ++j) {
// skip undefined values to preserve sparse array
if (original[i][j] === undefined) continue;
// create row if it doesn't exist yet
if (copy[j] === undefined) copy[j] = [];
// swap the x and y coords for the copy
copy[j][i] = original[i][j];
}
}
return copy;
}
function insertColor5() {
const sheetName = "結果１"; // Please set the sheet name.
// 1. Retrieve values from sheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const values1 = sheet.getRange(3, 7, sheet.getLastRow() - 2, sheet.getLastColumn()).getValues();
const values = transpose(values1);
//Logger.log(values);
// 2. Create an array for modifying the background colors.
const backgroundColors = values.map(([,,c,,,...g]) =>
g.reduce((o, e) => {
if (e.toString() != "") {
o.total += e;
o.colors.push(c >= o.total ? null : "red");
} else {
o.colors.push(null);
}
return o;
}, {colors: [], total: 0}).colors
);
const kolorki = transpose1(backgroundColors);
//Logger.log(transpose1(backgroundColors));
// 3. Modify the background colors of cells.
sheet.getRange(8, 7, kolorki.length, kolorki[0].length).setBackgrounds(kolorki);
}