Remove multiple columns from Google Sheets avoiding expensive loop

I am trying to avoid the following code because it is too slow:

 for (var c = 25; c>2; c--){

    if (sheet2.getRange(1,c).getValue() == 0) 

    {sheet2.deleteColumn(c)}

  }

Instead I tried to find a list of columns I want to delete from the array and then set the array. (I recently figure out that deleting rows/columns in a loop is very expensive: google script loop performance)

I found this Removing columns of data in javascript array and try to apply it to my code, but it is not working.

Here is the code.

  var ary = sheet2.getRange(2,1,outData.length+1,outData[0].length).getValues();
  var indexesToRemove = [];
  for (var c = 25; c>2; c--){

    if (sheet2.getRange(1,c).getValue() == 0)

    {
      indexesToRemove.push(c);

    }
  }

The part above works well. What is not working is the function to remove the columns from the array once I found the indexes to remove. The array _row is not what I am looking for. What am I doing wrong?

removeColumns(ary, indexesToRemove);}



function removeColumns(data, indexes) {
    return data.map(function (row) {
        // when we remove columns, the indexing gets off by 1 each time, keep track of how many to adjust
        var indexAdjustment = 0;
        // copy row w/ .slice so we do not modify the original array
        var _row = row.slice();
        indexes.forEach(function (colIndex) {
            // remove column
            _row.splice(colIndex - indexAdjustment, 1);
            // add 1 to adjustment to account for the column we just removed
            indexAdjustment++
        });
        return _row;
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet2 = ss.getSheetByName('Cache');  
      sheet2.clear();
      sheet2.getRange(2,1,_row.length,_row[0].length).setValues(_row);
    });
}

BTW, I have also tried this before, but still not working:

  var ary = sheet2.getRange(2,1,outData.length+1,outData[0].length).getValues();
  for (var c = 25; c>2; c--){

    if (sheet2.getRange(1,c).getValue() == 0)

    { ary = ary.map(function(item){
        return item.splice(0,c)});

    }
  }

Upvotes: 1

Answers (3)

Tanaike

Reputation: 201493

You want to delete the columns that the value of is 0 in the cells C1:Y1.
You want to reduce the process cost of the script.
You want to achieve this without using Sheets API.

Pattern 1:

In this pattern, at first, the cells which have the value of 0 from the cells C1:Y1 using TextFinder, and the columns are deleted from the retrieved cells using deleteColumn().

Sample script:

const sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange("C1:Y1")
  .createTextFinder(0)
  .matchEntireCell(true)
  .findAll()
  .reverse()
  .forEach(e => sheet.deleteColumn(e.getColumn()));

Pattern 2:

In this pattern, at first, all values are retrieved from "C1" to the last column for the all data rows, and delete the columns in the array and clear the range, and then, the values are put to the sheet. The method for directly processing the retrieved values has already been proposed. So as other pattern, I proposed the method which uses the transpose.

Sample script:

const sheet = SpreadsheetApp.getActiveSheet();

const range = sheet.getRange(1, 3, sheet.getLastRow(), sheet.getLastColumn() - 2);
const values = range.getValues();
const t = values[0].reduce((ar, r, i) => {
  if (r != 0) ar.push(values.map(c => c[i]));
  return ar;
}, []);
const v = t[0].map((_, i) => t.map(c => c[i]));
range.clearContent();
sheet.getRange(1, 3, v.length, v[0].length).setValues(v);

Pattern 3:

In this pattern, the request body for the batchUpdate method of Sheets API is created using the 1st row values, and the request body is used for requesting to Sheets API. By this, several columns can be deleted by one API call.

Before you run the script, please enable Sheets API at Advanced Google services.

Sample script:

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const sheetId = sheet.getSheetId();

// Create rerequests for DeleteDimensionRequest.
const requests = sheet.getRange("C1:Y1")
  .createTextFinder(0)
  .matchEntireCell(true)
  .findAll()
  .reverse()
  .map(e => {
    const col = e.getColumn();
    return {deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: col - 1, endIndex: col}}}
  });

// Request to the batchUpdate method using the request body.
Sheets.Spreadsheets.batchUpdate({requests: requests}, spreadsheet.getId());

In this case, requests is created using the method of pattern 1. Each request is as follows. You can see about this structure at the document.

{
  "deleteDimension": {
    "range": {
      "sheetId": "###",
      "dimension": "COLUMNS",
      "startIndex": ##,
      "endIndex": ##
    }
  }
}

References:

Upvotes: 2

Cooper

Reputation: 64092

function runOne() {
  var d=0;
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];//header array
  var vs=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();//data array
  vs.forEach(function(r,j){
    var d=0;
    hA.forEach(function(h,i){
      if(h==0)r.splice(i-d++,1);//removes elements in columns whose headers are == 0
    });
  });
  Logger.log(vs);
}

Upvotes: 1