2

I have a script which deletes columns of information that is now rendered obsolete.

However, I have multiple tabs which means going through them all manually, how can I edit my script to do this automatically for me, I have been trying for an hour or so now on Google trying other peoples solutions but because my coding skills is below basic I have no clue what I am doing.

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];

  var sheet = SpreadsheetApp.getActiveSheet();
  var width = sheet.getLastColumn();
  var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
  for (var i = headers.length - 1; i >= 0; i--) {
    if (required.indexOf(headers[i]) == -1) {
      sheet.deleteColumn(i+1);
    }
  }
}

Log 1 Log 2

0

Explanation:

You need to make two modifications:

  • Use getSheets to get all the sheets of your spreadsheet file.

  • Define a list of sheet names you would like to execute your current solution to:

    var selectedSheets = ["Sheet1","Sheet5","Test2"];

  • Create a for loop (in this case forEach) and iterate over the sheets.

  • Finally (optional), for every sheet check if the sheet name is included in the selectedSheets array you defined earlier.

Solution - Run on selected sheets:

Adjust selectedSheets to include the sheet names for which you want to run the script. Make sure the names match exactly the name of your sheets. No extra spaces or different lower/upper case letters.

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];
  var selectedSheets = ["Sheet1","Sheet5","Test2"]; // select the sheets you want to run the function for
  var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets
  // iterate over all sheets and execute code on selectedSheets
  sheets.forEach(sheet=>{
     if(selectedSheets.includes(sheet.getName())){           
        var width = sheet.getLastColumn();
        var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
        for (var i = headers.length - 1; i >= 0; i--) {
           if (required.indexOf(headers[i]) == -1) {
           sheet.deleteColumn(i+1);
        }
        }
     }
  });
}

Solution - Run on every sheet in the spreadsheet:

function deleteColumns() {
  var required = ["Owned",  "Set",  "Card number",  "Edition",  "Rarity",   "Name", "Card type",];
  var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets
  // iterate over all sheets and execute code on selectedSheets
  sheets.forEach(sheet=>{
        var width = sheet.getLastColumn();
        var headers = sheet.getRange(1, 1, 1, width).getValues()[0];
        for (var i = headers.length - 1; i >= 0; i--) {
           if (required.indexOf(headers[i]) == -1) {
           sheet.deleteColumn(i+1);
        }
        }
  });
}
5
  • I used the script you had created and it is coming up with the error I was getting when I was trying to get it to work on my own: 'Attempted to execute deleteColumns, but could not save.' – Jesse Jan 12 at 1:54
  • I have included pictures on what I am running, and how I am running it as well as the errors that are coming up when I try to run. I am running in the google script editor and not as a macro in sheets. please see original post for pictures. – Jesse Jan 12 at 2:05
  • @Jesse is the script attached to a spreadsheet file or it is a standalone project? how did you go to the script editor ? via a spreadsheet or you created a new project? – Marios Jan 12 at 2:09
  • the script is attached to the spreadsheet, and I went to the script editor via the spreadsheet: Tools> Script Editor – Jesse Jan 12 at 2:12
  • Let us continue this discussion in chat. – Marios Jan 12 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.