im trying to clean some sheets, individually i make it work uncommenting and changing sheet name
function doClean(sheet)
{
// var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Resultado");
var LR = sheet.getLastRow();
var LC = sheet.getLastColumn();
sheet.getRange(1,1,LR,LC).getDisplayValues()
sheet.createTextFinder("-").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0,00").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0,0000").matchEntireCell(true).replaceAllWith("");
};
but when when i try to group in array and execute with foreach
function doCleanSheets() {
var sheet = ["BLC", "Balanço", "DRE", "Resultado", "FLC", "Fluxo", "DVA", "Valor"];
SpreadsheetApp.getActive().sheet.forEach(doClean);
};
im getting error
TypeError: Cannot read property 'forEach' of undefined doCleanSheets @ - 10x_to_11x.gs:87
line 87 is SpreadsheetApp.getActive().sheet.forEach(doClean);
searched for error, but results were way more complex than my case, and i couldnt apply
CodePudding user response:
When I saw your script, unfortunately, SpreadsheetApp.getActive()
has no property of sheet
. By this, such error occurs. When you want to use the sheet names of sheet
in the forEach, how about the following modification?
Modified script:
Please modify doCleanSheets
as follows.
function doCleanSheets() {
var sheet = ["BLC", "Balanço", "DRE", "Resultado", "FLC", "Fluxo", "DVA", "Valor"];
var sheets = SpreadsheetApp.getActive().getSheets().filter(s => sheet.includes(s.getSheetName()));
sheets.forEach(doClean);
}
References:
CodePudding user response:
Try it this way:
function doClean(sheet) {
var LR = sheet.getLastRow();
var LC = sheet.getLastColumn();
sheet.getRange(1, 1, LR, LC).getDisplayValues()
sheet.createTextFinder("-").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0,00").matchEntireCell(true).replaceAllWith("");
sheet.createTextFinder("0,0000").matchEntireCell(true).replaceAllWith("");
};
function doCleanSheets() {
var sheet = ["BLC", "Balanço", "DRE", "Resultado", "FLC", "Fluxo", "DVA", "Valor"];
sheet.forEach(sh => doClean(SpreadsheetApp.getActive().geSheetByName(sh)));
};