Delete specific columns in Google App Script FAST-CodePudding

below is code the delete entire columns except columns "G" and "M" using a for loop, yet the process is the way too slow, is there a way to perform it faster?

  var lastCol = newSheet.getLastColumn();
  var keep = [7,13]; 

  for (var col=lastCol; col > 0; col--) {
  if (keep.indexOf(col) == -1) {
  newSheet.deleteColumn(col);
                }
     }

CodePudding user response：

Here's how to do that with the SpreadsheetApp API:

function test() {
  const keep = [7, 13]; // columns G and M
  const newSheet = SpreadsheetApp.getActiveSheet();
  deleteColumns_(newSheet, keep);
}


/**
* Deletes all columns in sheet except the ones whose column numbers
* are listed in columnsToKeep.
*
* The columnsToKeep array [1, 2, 7, 13] means that columns A, B, G and M
* will remain while other columns are deleted.
*
* @param {Sheet} sheet The sheet where to delete columns.
* @param {Number[]} columnsToKeep Array of column numbers to keep.
*/
function deleteColumns_(sheet, columnsToKeep) {
  // version 1.0, written by --Hyde, 13 June 2022
  //   - see https://stackoverflow.com/q/72600890/13045193
  const columnsToDelete = [];
  for (let i = 1, maxColumns = sheet.getMaxColumns(); i <= maxColumns; i  ) {
    if (!columnsToKeep.some(columnNumber => i === columnNumber)) {
      columnsToDelete.push(i);
    }
  }
  const tuples = getRunLengths_(columnsToDelete).reverse();
  tuples.forEach(([columnStart, numColumns]) => sheet.deleteColumns(columnStart, numColumns));
}


/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the number of items in each run.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* For best results, sort the numbers array like this:
* const runLengths = getRunLengths_(numbers.sort((a, b) => a - b));
* Note that duplicate values in numbers will give duplicates in result.
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs, or [] if the array is empty.
*/
function getRunLengths_(numbers) {
  // version 1.1, written by --Hyde, 31 May 2021
  if (!numbers.length) {
    return [];
  }
  return numbers.reduce((accumulator, value, index) => {
    if (!index || value !== 1   numbers[index - 1]) {
      accumulator.push([value]);
    }
    const lastIndex = accumulator.length - 1;
    accumulator[lastIndex][1] = (accumulator[lastIndex][1] || 0)   1;
    return accumulator;
  }, []);
}

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.
You want to delete all columns except for columns "G" and "M".

In this case, how about using Sheets API? When Sheets API is used, your script is as follows. I thought that when Sheets API is used, the process cost might be able to be reduced a little.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  var sheetName = "Sheet1";  // Please set the sheet name.
  var keep = [7, 13];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.getSheetByName(sheetName);
  var lastCol = newSheet.getLastColumn(); // or newSheet.getMaxColumns()
  var sheetId = newSheet.getSheetId();
  var requests = [...Array(lastCol)].reduce((ar, _, i) => {
    if (!keep.includes(i   1)) {
      ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i   1, dimension: "COLUMNS" } } });
    }
    return ar;
  }, []).reverse();
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

When this script is run, all columns except for the columns "G" and "M" are deleted.
From your script, var lastCol = newSheet.getLastColumn(); is used. In this case, the data range is used. If you want to check all columns, please use var lastCol = newSheet.getMaxColumns(); instead of it.

Modified script:

References: