0

I have no background in programming but I am trying to come up with a Script on Google Sheets to remove the first 3 columns in my Workbook except the "Main Menu". I have 61 tabs so I didn't want to individually remove them. So, the following is what I managed to come up with from doing the googling. I kept seeing the error "Cannot find method deleteColumns()." Any help would be much appreciated.

function QuickDelete() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sss = ss.getSheets();

  for (i = 0; i < sheets.length; i++) {
     switch(sss[i].getSheetName()) {
     case "Main Menu":
         break;
     default:
        ss.deleteColumns(sss[i]);
    }
  }
}
player0
  • 69,261
  • 8
  • 33
  • 67
Fatin Nabilah
  • 3
  • 2

2 Answers2

1

I see two problems that need to be fixed.

  1. You are looping over sheets. But you should be looping over sss.
  2. The deleteColumns() method needs to be applied to the sheet i.e. sss[i]

Something like sss[i].deleteColumns(columnPosition, howMany).

function QuickDelete() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sss = ss.getSheets();
  var columnPosition = 1; // Delete from the 1st column
  var howMany = 3; // Delete 3 columns

  for (i = 0; i < sss.length; i++) {
    switch(sss[i].getSheetName()) {
      case "Main Menu":
        break;
      default:
        sss[i].deleteColumns(columnPosition, howMany)
    }
  } 
}

All the best.

ADW
  • 3,327
  • 1
  • 7
  • 16
0

  • In your for loop, your are using sheets, which you haven't defined before. You have to change sss to sheets or viceversa everywhere in your code.

  • You have to provide two parameters to deleteColumns, the position of the first column to delete and the number of columns to delete. In your case, 1 and 3 respectively.

So your code could be like this:

function QuickDelete() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== "Main Menu") {
      sheets[i].deleteColumns(1, 3)
    }
  }
}

Also, the switch statement makes sense if there are many values you have to check your expression with. In your case, I'd better use if to check if the name of the sheet is Main Menu.

  • Reference:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteColumns(Integer,Integer) https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/switch https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/if...else

Iamblichus
  • 11,686
  • 2
  • 7
  • 24
  • I tried your method as well as ADW's and both method works! Also, I was wondering what does the !== notation mean? – Fatin Nabilah Nov 29 '19 at 03:17
  • @FatinNabilah check [this](https://stackoverflow.com/questions/1889260/javascript-operator). Also, please consider accepting one of the answers that provided the solution to your problem by [clicking the "check mark" button underneath the vote buttons](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work), as this community relies on this to share knowledge to other users. – Iamblichus Nov 29 '19 at 07:56