Skip to content

Instantly share code, notes, and snippets.

@awwsmm
Last active September 11, 2022 18:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save awwsmm/fd885bac8e4ca78f79a7a280b818e24f to your computer and use it in GitHub Desktop.
Save awwsmm/fd885bac8e4ca78f79a7a280b818e24f to your computer and use it in GitHub Desktop.
Google Apps Script macro to clear contents in a Google Sheet, skipping protected columns and rows
/** @OnlyCurrentDoc */
clearTransactions(3, 1, 6, 6, true);
function clearTransactions(nHeaderRows, nHeaderColumns, nAccountsPerSection, nRowsPerDay, DEBUG = false) {
// BEGIN https://stackoverflow.com/a/21231012/2925434
function columnToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
// END https://stackoverflow.com/a/21231012/2925434
// Google Sheets columns are numbered starting from 1
var sheet = SpreadsheetApp.getActive().getSheetByName("daily");
// get protected columns
var maxColumn = sheet.getLastColumn();
var protectedColumns = [];
function isProtectedColumn(index) {
return index <= nHeaderColumns || (index - nHeaderColumns) % (nAccountsPerSection + 1) == 0;
}
for (var col = 1; col <= maxColumn; ++col) {
if (isProtectedColumn(col)) {
protectedColumns.push(col);
}
}
if (DEBUG) {
Logger.log("protected columns: " + protectedColumns.map( each => columnToLetter(each) ));
}
// get protected rows
var maxRow = sheet.getLastRow();
var protectedRows = [];
function isProtectedRow(index) {
return index <= nHeaderRows || (index - nHeaderRows) % (nRowsPerDay + 1) == 0;
}
for (var row = 1; row <= maxRow; ++row) {
if (isProtectedRow(row)) {
protectedRows.push(row);
}
}
if (DEBUG) {
Logger.log("protected rows: " + protectedRows);
}
var ranges = [];
for (var top = 0; top < (protectedRows.length - 1); ++top) {
var topIncl = protectedRows[top] + 1;
var botIncl = protectedRows[top+1] - 1;
if (botIncl - topIncl < 0) { continue; }
for (var left = 0; left < (protectedColumns.length - 1); ++left) {
var leftIncl = protectedColumns[left] + 1;
var rightIncl = protectedColumns[left+1] - 1;
if (rightIncl - leftIncl < 0) { continue; }
var range = columnToLetter(leftIncl) + topIncl + ":" + columnToLetter(rightIncl) + botIncl;
if (DEBUG) {
Logger.log("range " + range + " will be cleared when DEBUG == false");
} else {
ranges.push(range);
// currently no way to clear comments
sheet.getRangeList(ranges).clearContent();
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment