Tengo un libro de trabajo con varias hojas, y la hoja principal tiene 123 filas y 90 columnas actualmente.
He codificado las siguientes funciones (que funcionan) para definir un controlador de eventos onChange para el evento INSERT_COLUMN que llena automáticamente las celdas de la columna recién insertada con el contenido de la columna inmediatamente a la izquierda. Luego borra los valores de las celdas que no son fórmulas.
Es dolorosamente lento, y no estoy seguro de por qué.
¿Cómo puedo acelerar esto? Gracias.
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); } } }No estoy seguro si entiendo completamente el problema. Así que aquí hay una conjetura.
Intentaría cambiar la función con el nombre elegante deleteAllValuesAndNotesFromNonFormulaCells() esta manera:
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); }Si no necesita conservar las notas, la función puede reducirse a una sola línea:
function deleteAllValuesAndNotesFromNonFormulaCells(range) { range.setFormulas(range.getFormulas()).clearNote(); }Descripción
No entiendo la necesidad de mucho de lo que ha desarrollado el OP. Pero aquí hay un ejemplo de insertar una columna a la derecha, tomar la columna a la izquierda y copiarla en la nueva columna. Luego elimine cualquier valor o nota dejando solo las fórmulas.
Dado que getFormulas() devuelve una matriz 2D de cadenas que representan las fórmulas en el rango, simplemente usar setValues(formulas) coloca las fórmulas en las celdas.
Código.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); } }Referencia