0

I import a csv file into the sheet which I then clean up of any columns I do not need by using the function below. I want to extend the functionality of this script.

After deleting those columns using the function below, I want to be able to run a separate function that does the following:

  1. Make all cells in the first row become a Data Filter. Ideally this row should be frozen.
  2. After the Data Filter is set, I want to make 5 duplicates of this sheet so I can use them to create different reports within the same file.
  3. I plan to manually adjust the filter values in each sheet. However kudos if the script is able to sort the "Name" column as A-Z in one sheet and in a different sheet sort the "Salary" column as Z-A.

The following script adds a menu item that allows you to run the deleteColumns function. This function deletes all columns which do not have a header that matches the values for the "required" variable. Below is basically the 1st step of my process when preparing reports. This question is solve Step 2 in the process.

 function onOpen() {
   var spreadsheet = SpreadsheetApp.getActive();
   var menuItems = [
                   {name: 'Delete Columns', functionName: 'deleteColumns'}
   ];
   spreadsheet.addMenu('Prep Report', menuItems);
 }

function deleteColumns() {
  var required = ["Name", "Position", "Salary"];

  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);
    }
  }
}

You can learn more about the above script here.

  • Is this a follow-up question to the other one? If not, there's no reason to repost the answer to that question as another question. – jonsca Jul 10 '17 at 11:26
  • Yes this is a follow up question on how to add some additional functionality. The other question accomplished one function, this question is about adding another function to the same script. – Damainman Jul 10 '17 at 16:51

Your Answer

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

Browse other questions tagged or ask your own question.