Skip to content

Instantly share code, notes, and snippets.

@YamilG
Created September 23, 2021 16:39
Show Gist options
  • Save YamilG/d5828a8af0c88ca908046f824cc21fd5 to your computer and use it in GitHub Desktop.
Save YamilG/d5828a8af0c88ca908046f824cc21fd5 to your computer and use it in GitHub Desktop.
function cleansheet() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B:B').activate();
spreadsheet.getActiveSheet().sort(2, true);
spreadsheet.getActiveSheet().hideColumns(1);
spreadsheet.getActiveSheet().hideColumns(3);
spreadsheet.getActiveSheet().hideColumns(5,7);
spreadsheet.getActiveSheet().setColumnWidth(2, 230);
spreadsheet.getRange('D:D').activate();
spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 1);
spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 1).activate();
spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 1);
spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 1).activate();
spreadsheet.getActiveSheet().insertColumnsAfter(spreadsheet.getActiveRange().getLastColumn(), 1);
spreadsheet.getActiveRange().offset(0, spreadsheet.getActiveRange().getNumColumns(), spreadsheet.getActiveRange().getNumRows(), 1).activate();
spreadsheet.getRange('F1').activate();
spreadsheet.getCurrentCell().setFormula('=UNIQUE(B1:B1500)');
spreadsheet.getRange('G1').activate();
spreadsheet.getCurrentCell().setFormula('=SUMIF(B$1:B$1500,F1,D$1:D$1500)');
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('G1:G100'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('F:F').activate();
spreadsheet.getActiveSheet().setColumnWidth(6, 186);
spreadsheet.getActiveSheet().setColumnWidth(7, 174);
spreadsheet.getRange('G:G').activate();
spreadsheet.getActiveRangeList().setNumberFormat('0.0')
.setHorizontalAlignment('left');
spreadsheet.getRange('B:B').activate();
spreadsheet.getActiveSheet().hideColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
spreadsheet.getRange('D:D').activate();
spreadsheet.getRange('D:E').activate();
spreadsheet.getActiveSheet().hideColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
spreadsheet.getRange('F:F').activate();
spreadsheet.getActiveSheet().setColumnWidth(7, 71);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment