1

I have a large spreadsheet with hundreds of columns. About half of the column headings have the word "alone" in them. I need to delete all of the columns that contain "alone" in the header row. Is there a way to accomplish this?

2
  • If you will share a link to the sheet here, being sure to set permission as "Anyone with the link can Edit" (not just "... can View"), I or someone else here can help you. In my thinking, you don't need a script. I could accomplish this in about two minutes, if I had access to the sheet (while explaining how would take 20 times as long).
    – Erik Tyler
    Dec 10 '18 at 22:21
  • 1
    I've genericized the question to make it about the problem to be solved, rather than how to find a pre-selected solution.
    – ale
    Jan 2 '19 at 15:49
0

no. however,

you can take it from the other end and create a sheet with a query that will exclude all "alone" columns like:

=ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(TO_TEXT(A1:D));"select * where Col1 <>'alone' 
                                                                   and Col2 <>'alone' 
                                                                   and Col3 <>'alone'
                                                                   and Col4 <>'alone'")))
etc...

1
  • 1
    Why would you rollback that edit? Non-standard capitalization and punctuation makes posts harder to read.
    – ale
    Jan 2 '19 at 17:26
0

Yes, there is, actually there are several ways. Maybe the simplest is by using Google Apps Script and a JavaScript's for and if statements.

Here is an example of a basic script (untested) that will, on the active sheet, loop backward looking for cells on the first row (headers) having alone as value, if this occurs, then it will delete the column otherwise will pass to the next header.

function deleteAloneColumns(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumnPos = sheet.getLastColumn();
  var headers = sheet.getRange( 1 ,1, 1, lastColumnPos ).getValues()[0];
  for( var i = lastColumnPos ; i < 1; i--){
    if( headers[i] === "alone" ) sheet.deleteColumn(i);
  }
 SpreadsheetApp.getUi().alert( 'Job done!' );
}

References

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.