如何加快速度驚人的慢谷歌表格應用程式腳本功能，將公式從一列復制到新插入的列

我有一個包含多張作業表的作業簿，主作業表目前有 123 行和 90 列。

我撰寫了以下函式（有效）來為事件定義一個onChange事件處理程式，該事件處理程式INSERT_COLUMN自動填充新插入的列的單元格，該列的內容緊靠左側。然后它洗掉不是公式的單元格的值。

它非常緩慢，我不知道為什么。

我怎樣才能加快速度？謝謝。

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);
    }
  }
}

uj5u.com熱心網友回復：

描述

我不明白對 OP 開發的很多東西的需求。但這里是一個在右側插入一列的示例，將其左側的列復制到新列中。然后消除任何值或注釋，只留下公式。

由于 getFormulas() 回傳表示范圍內公式的二維字串陣列，因此使用 setValues(formulas) 將公式放入單元格中。

代碼.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);
  }
}

參考

• Range.copyTo()
• Range.getLastRow()
• Range.getFormulas()
• Range.setValues()

uj5u.com熱心網友回復：

不確定我是否完全理解這個問題。所以這里有一個猜測。

我會嘗試以deleteAllValuesAndNotesFromNonFormulaCells()這種方式更改具有花哨名稱的函式：

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);
}

如果您不需要保留筆記，則該功能可以歸結為一行：

function deleteAllValuesAndNotesFromNonFormulaCells(range) {
  range.setFormulas(range.getFormulas()).clearNote();
}

標籤：javascript 表现 谷歌应用脚​​本 谷歌表格

上一篇：無法讓復選框作業谷歌表格腳本

下一篇：使用Google應用腳本從表單填充Google電子表格