Trouble deploying sheets addon

Question

Our website spits out CSV files that we use as event rosters, but they include way too much data. There's no feasible way to change what's included in the export, so an admin assistant must edit & format for print. It's repetitive and time consuming, so I figured it's the perfect time to learn Google Apps Script.

Thanks to the incredible knowledge shared here on stack overflow, a total noob like me can cobble together a script that does what I need! Just by using snippets from other answers, I was able to automate:

• Delete unwanted & empty columns
• Rename & auto-resize columns
• Sort by the last name column
• Generate print-ready PDF that saves in the same Drive directory.

But now I'm having trouble testing and deploying the script as an addon so my co-workers can use it. When I run a "test as addon" the sheet opens, but nothing happens. I've tried all the variables for installation config and searched for others having the same trouble, but can't find anything so I think the problem is prbly somewhere on my end - script or user error.

Once I get it to test correctly, I'm not entirely sure about how to correctly deploy the addon to our domain and get all the permissions, etc setup correctly. I've read up and now I feel more confused than ever! So two questions:

• What's wrong w/ my testing?
• Once it tests successfully, what's the easiest way I can let all of our domain's apps users utilize the script?

Here's the script:

function expCalc() {
  DeleteColumns();
  RemoveEmptyColumns();
  RenameColumns();
  ResizeColumns();
  Sort();
  SavePDF();
}

//delete unwanted columns
function DeleteColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getRange("A1:AH200");
  var data = sheet.getRange("A1:AH200");
  var values = data.getValues();
  var numRows = values.length;
  var numCols = values[0].length;
  for (var col = numCols - 1; col > 0; col--) {
    for (var row = 0; row < numRows; row++) {
      switch (values[row][col]) {
        case "Group":
        case "ID":
        case "Reg ID":
        case "Reg Date":
        case "Type of Payment":
        case "Transaction ID":
        case "Coupon Code":
        case "# Attendees":
        case "Date Paid":
        case "Price Option":
        case "Event Date":
        case "Event Time":
        case "Website Check-in":
        case "Tickets Scanned":
        case "Check-in Date":
        case "Seat Tag":
        case "BLS Add-on items (received at class):":
        case "Company Name":
        case "Address":
        case "Address 2":
        case "City":
        case "State":
        case "Zip":

          sheet.deleteColumn(col + 1); // delete column in sheet (1-based)
          continue; // continue with next column
          break; // can't get here, but good practice
      }
    }
  }
}

//Remove Empty Columns
function RemoveEmptyColumns() {
  var sh = SpreadsheetApp.getActiveSheet();
  var maxColumns = sh.getMaxColumns();
  var lastColumn = sh.getLastColumn();
  sh.deleteColumns(lastColumn + 1, maxColumns - lastColumn);
}

//Rename Columns
function RenameColumns() {
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue('Type');
  SpreadsheetApp.getActiveSheet().getRange('B1').setValue('Paid');
  SpreadsheetApp.getActiveSheet().getRange('C1').setValue('Price');
  SpreadsheetApp.getActiveSheet().getRange('D1').setValue('Amt');
  SpreadsheetApp.getActiveSheet().getRange('E1').setValue('Class');
  SpreadsheetApp.getActiveSheet().getRange('F1').setValue('First Name');
  SpreadsheetApp.getActiveSheet().getRange('G1').setValue('Last Name');
  SpreadsheetApp.getActiveSheet().getRange('H1').setValue('Email');
  SpreadsheetApp.getActiveSheet().getRange('I1').setValue('Phone');
}

//Auto-Resize Columns
function ResizeColumns() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  sheet.autoResizeColumn(1);
  sheet.autoResizeColumn(2);
  sheet.autoResizeColumn(3);
  sheet.autoResizeColumn(4);
  sheet.autoResizeColumn(5);
  sheet.autoResizeColumn(6);
  sheet.autoResizeColumn(7);
  sheet.autoResizeColumn(8);
  sheet.autoResizeColumn(9);
}

//Sort by last name
function Sort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  sheet.sort(7);
}

//Save PDF
function SavePDF(optSSId, optSheetId) {

  // If a sheet ID was provided, open that sheet, otherwise assume script is
  // sheet-bound, and open the active spreadsheet.
  var ss = (optSSId) ? SpreadsheetApp.openById(optSSId) : SpreadsheetApp.getActiveSpreadsheet();

  // Get URL of spreadsheet, and remove the trailing 'edit'
  var url = ss.getUrl().replace(/edit$/, '');

  // Get folder containing spreadsheet, for later export
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  } else {
    folder = DriveApp.getRootFolder();
  }

  // Get array of all sheets in spreadsheet
  var sheets = ss.getSheets();

  // Loop through all sheets, generating PDF files.
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];

    // If provided a optSheetId, only save it.
    if (optSheetId && optSheetId !== sheet.getSheetId()) continue;

    //additional parameters for exporting the sheet as a pdf
    var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf
      +
      '&gid=' + sheet.getSheetId() //the sheet's Id
      // following parameters are optional...
      +
      '&size=letter' // paper size
      +
      '&portrait=false' // orientation, false for landscape
      +
      '&fitw=true' // fit to width, false for actual size
      +
      '&sheetnames=false&printtitle=false&pagenumbers=false' // hide optional headers and footers
      +
      '&gridlines=true' // hide/show gridlines
      +
      '&fzr=false'; // do not repeat row headers (frozen rows) on each page

    var options = {
      headers: {
        'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
      }
    }

    var response = UrlFetchApp.fetch(url + url_ext, options);

    var blob = response.getBlob().setName(ss.getName() + ' - ' + sheet.getName() + '.pdf');

    folder.createFile(blob);
  }
}

/**
 * Dummy function for API authorization only.
 * From: https://stackoverflow.com/a/37172203/1677912
 */
function forAuth_() {
  DriveApp.getFileById("Just for authorization"); // https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579#c36
}

Answer 1

Great use for an add-on. In order to make it work as an add-on you need to create an onOpen() trigger so that the users can interact with your code.

Refer to the onOpen() docs here: https://developers.google.com/apps-script/guides/triggers/#onopen

See an example here: https://developers.google.com/apps-script/add-ons/#user_interfaces