The problem I'm trying to solve is the following. I'm trying to make a loop which finds the letter "Y" and only the Y. I want to make it so if a cell has Y in it, it will make the 4 rows to the left of it be called %NULL%. The issue is there are 2 cells per row where Y could be.
This is the code I started but don't know where to go from.
function Loop1(){
var sheet = SpreadsheetApp.getActive().getSheetByName("EXP50");
var data = sheet.getDataRange().getValues();
for(var i = 1; i< data.length; i ){
if(data[i][7-1] === "Y"){
sheet.getRange(i 1, 3).setValue("%NULL%");
sheet.getRange(i 1, 4).setValue("%NULL%");
sheet.getRange(i 1, 5).setValue("%NULL%");
sheet.getRange(i 1, 6).setValue("%NULL%");
}
if(data[i][13-1] === "Y") {
sheet.getRange(i 1, 9).setValue("%NULL%");
sheet.getRange(i 1, 10).setValue("%NULL%");
sheet.getRange(i 1, 11).setValue("%NULL%");
sheet.getRange(i 1, 12).setValue("%NULL%");
}
}
}
CodePudding user response：
Instead of a if loop, couldn't you use something like this:
function Loop1(){
var sheet = SpreadsheetApp.getActive().getSheetByName("EXP50");
var data = sheet.getDataRange().getValues();
while 1 = 1:
if(data[i][7-1] === "Y"){
sheet.getRange(i 1, 3).setValue("%NULL%");
sheet.getRange(i 1, 4).setValue("%NULL%");
sheet.getRange(i 1, 5).setValue("%NULL%");
sheet.getRange(i 1, 6).setValue("%NULL%");
}
if(data[i][13-1] === "Y") {
sheet.getRange(i 1, 9).setValue("%NULL%");
sheet.getRange(i 1, 10).setValue("%NULL%");
sheet.getRange(i 1, 11).setValue("%NULL%");
sheet.getRange(i 1, 12).setValue("%NULL%");
}
}
}
(Apologies, I do not know if this will resolve your issue.)
CodePudding user response：
I believe your goal is as follows.
- You want to check the columns "G" and "M".
- When the cell value of the columns is "Y", you want to put
%NULL%to the columns "C" - "F" and the columns "I" - "L" in the same row. - You want to achieve this using Google Apps Script.
Modification points:
- I thought that
setValueis used in the loop, the process cost will become high.
In this answer, by reducing the process cost, I would like to propose the following 2 patterns.
Pattern 1:
In this pattern, the methods of getValues and setValues are used.
function Loop1() {
var sheet = SpreadsheetApp.getActive().getSheetByName("EXP50");
var range = sheet.getDataRange();
var data = range.getValues();
data.forEach(r => {
var [g, m] = [r[6], r[12]];
var temp = Array(4).fill("%NULL%");
if (g == "Y") r.splice(2, 4, ...temp);
if (m == "Y") r.splice(8, 4, ...temp);
});
range.setValues(data);
}
Pattern 2:
In this pattern, TextFinder is used.
function Loop1() {
var sheet = SpreadsheetApp.getActive().getSheetByName("EXP50");
var ranges = sheet.createTextFinder("Y").matchCase(true).matchEntireCell(true).findAll().reduce((ar, r) => {
var col = r.getColumn();
var row = r.getRow();
if (col == 7) ar.push(`C${row}:F${row}`);
if (col == 13) ar.push(`I${row}:L${row}`);
return ar;
}, []);
sheet.getRangeList(ranges).setValue("%NULL%");
}