I have a workbook with multiple sheets, and the main sheet has 123 rows and 90 columns currently.
I've coded the following functions (which work) to define an onChange event handler for the INSERT_COLUMN event that automatically populates the cells of the newly-inserted column with the contents of the column immediately to the left. Then it deletes the values of the cells that are not formulas.
It's painfully slow, and I'm not sure why.
How can I speed this up? Thanks.
function getColumnLetter(a1Notation) {
const letter = a1Notation.replace(/\d+/, '');
return letter;
}
function getColumnLetterFromNumber(sheet, colNum) {
const range = sheet.getRange(1, colNum);
return getColumnLetter(range.getA1Notation());
}
function forEachRangeCell(range, callback) {
const numRows = range.getNumRows();
const numCols = range.getNumColumns();
for (let i = 1; i <= numCols; i+=1) {
for (let j = 1; j <= numRows; j+=1) {
const cell = range.getCell(j, i);
callback(cell);
}
}
}
function deleteAllValuesAndNotesFromNonFormulaCells(range) {
forEachRangeCell(range, function (cell) {
if(!cell.getFormula()){
cell.setValue(null);
cell.clearNote();
}
});
}
function onInsertColumn(sheet, activeRng) {
if (activeRng.isBlank()) {
const minCol = 5;
const col = activeRng.getColumn();
if (col >= minCol) {
const prevCol = col - 1;
const colLetter = getColumnLetterFromNumber(sheet, col);
const prevColLetter = getColumnLetterFromNumber(sheet, prevCol);
//SpreadsheetApp.getUi().alert(`Please wait while formulas are copied to the new column...`);
const originRng = sheet.getRange(`${prevColLetter}:${prevColLetter}`);
originRng.copyTo(activeRng, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
deleteAllValuesAndNotesFromNonFormulaCells(activeRng);
const completeMsg = `New column ${colLetter} has formulas copied and is ready for new values (such as address, Redfin link, data, ratings).`;
//SpreadsheetApp.getUi().alert(completeMsg);
// SpreadsheetApp.getActiveSpreadsheet().toast(completeMsg);
}
}
}
function onChange(event) {
if(event.changeType === 'INSERT_COLUMN'){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet()
const colNumber = sheet.getSelection().getActiveRange().getColumn();
const activeRng = sheet.getRange(1,colNumber,sheet.getMaxRows(),1);
const sheetName = sheet.getName();
if(sheetName === 'ratings'){
onInsertColumn(sheet, activeRng);
}
}
}
Not sure if I fully understand the problem. So here is a guess.
I'd try to change the function with the fancy name deleteAllValuesAndNotesFromNonFormulaCells() this way:
function deleteAllValuesAndNotesFromNonFormulaCells(range) {
// get the array with all formulas
var formulas = range.getFormulas();
// set all formulas back (it will clear all cells with no formula)
range.setFormulas(formulas);
// get the array with all notes and
// clear the ements of the 'notes' array that are empty in the array 'formulas'
var notes = range.getNotes().map((x,i) => formulas[i][0] ? x : ['']);
// set the modified array 'notes' back on the sheet
range.setNotes(notes);
}
If you don't need to keep the notes the function can be boiled down to just one line:
function deleteAllValuesAndNotesFromNonFormulaCells(range) {
range.setFormulas(range.getFormulas()).clearNote();
}
Description
I don't understand the need for a lot of what the OP has developed. But here is an example of inserting a column to the right, take the column to the left of it and copy it to the new column. Then eliminate any values or notes leaving only the formulas.
Since getFormulas() returns a 2D array of strings representing the formulas in the range simpy using setValues(formulas) places the formulas into the cells.
Code.gs
function onChange(e) {
try {
if( e.changeType === "INSERT_COLUMN" ) {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getActiveSheet();
if( sheet.getName() === "Sheet1" ) {
// assume insert column to the right
let colNumber = sheet.getSelection().getActiveRange().getColumn();
if( colNumber >= 5 ) {
let activeRng = sheet.getRange(1,colNumber,sheet.getLastRow(),1);
let originRng = sheet.getRange(1,colNumber-1,sheet.getLastRow(),1);
originRng.copyTo(activeRng);
let formulas = activeRng.getFormulas();
activeRng.setValues(formulas);
activeRng.clearNote();
}
}
}
}
catch(err) {
SpreadsheetApp.getUi().alert(err);
}
}
Reference