Delete columns

[John]

Hello Can some assist with creating an Apps script to find the last column in a sheet which has data in row 1 and to delete the rest of the columns to the right.

Or alternatively add the last column number in the script ie 26 for column "Z" and delete everything to the right of column 26(Z)

Any help is much appreciated

[Jonathan Butler]

Hi,

Do you have an example sheet?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/630c97c1-d561-4cea-aa7b-8bf8d045e822n%40googlegroups.com.

[John]

Sorry no example sheet that I can share as of yet. 

Is something not clear in my description?

[Jonathan Butler]

"  find the last column in a sheet which has data in row 1 and to delete the rest of the columns to the right.  " Is somewhat unclear. You say you want to delete columns to the right, it sounds like you want to remove all the columns for all the rows to the right of the selected column. Do you want to delete all the data in the cells instead? 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/32f210f0-335b-49d2-92b9-6f4ff659af24n%40googlegroups.com.

[John]

So, a script will look across row 1 to find the last column with data i.e. cell Z1 has a date but AA1 is empty.  

I then want to delete all columns to the right of the last column with data "Z1" to the end of columns

I am thinking that the last column can identified either by entering the last column number in the script (which would the same on all sheets) or checking for data in row 1. 

There can be numerous columns after the last data column which are just blank and I want to remove them all so that google sheets only shows columns with data in

 them.

Sample sheet

[Stephen Barker]

If you know Z1 has data and AA1 does not you can just use the normal sheet.getLastColumn()(commented out in code) but if AA2 has data and you want to still delete it that's a pain on dynamic data ranges but, if like you said you know it's always going to be Z1 you want to keep just update the col data variable at the top:

const COL_TO_DELETE_AFTER = 26 //a=1,j=10,z=26...

//Add to Menu

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Sheet Tools')

      .addItem('Remove Empty Columns', 'removeEmptyColumns')

      .addSeparator()

      .addItem('Remove Empty Rows', 'removeEmptyRows')

      .addToUi();

}

//Remove All Empty Columns in the Entire Workbook

function removeEmptyColumns() {

  var ss = SpreadsheetApp.getActive();

  var allsheets = ss.getSheets();

  for (var s in allsheets){

    var sheet=allsheets[s]

    var maxColumns = sheet.getMaxColumns(); 

    //var lastColumn = sheet.getLastColumn();

    var lastColumn = COL_TO_DELETE_AFTER;

    if (maxColumns-lastColumn != 0){

      sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);

    }

  }

}

//Remove All Empty Rows in the Entire Workbook

function removeEmptyRows() {

  var ss = SpreadsheetApp.getActive();

  var allsheets = ss.getSheets();

  for (var s in allsheets){

    var sheet=allsheets[s]

    var maxRows = sheet.getMaxRows(); 

    var lastRow = sheet.getLastRow();

    if (maxRows-lastRow != 0){

      sheet.deleteRows(lastRow+1, maxRows-lastRow);

    }

  }

}

 

[John]

Thanks for the support

Using the  sheet.getLastColumn() works as I need and clears any columns after Z1.

much appreciated

[Antony Lo]

This is great - thank you for this script!