search

nerc-project / operations

Issues related to the operation of the NERC OpenShift environment
1 stars 0 forks source link

Update Invoice PDF generation to update the column headers #561

Closed joachimweyl closed 1 month ago

joachimweyl commented 1 month ago

Motivation

Current PDFs are missing some of the header text

Completion Criteria

update the automation to increase the size of the header and auto adjust the width

Description

Completion dates

Desired - 2024-05-29 Required - TBD

joachimweyl commented 1 month ago

issue, the way I am exporting using blob seems to affect the form prior to export, need to find a new way to export.

joachimweyl commented 1 month ago

@waygil I am unable to find an automatic way to print PDFs without the headers getting modified. I was able to correct the tables that are created but the PDF still has unhelpful headers. Would providing CSVs work?

waygil commented 1 month ago

Hi @joachimweyl

Thanks for looking into this. PDFs would be preferrable as CSVs will be harder to read. Perhaps we can discuss more on Friday at our meeting.

joachimweyl commented 1 month ago

@waygil I figured it out, I had to separate the loop for cleaning and generating PDFs and now it generates PDFs with the correct formatting.

joachimweyl commented 1 month ago

Code

/**
V5
2024 Joachim Weyl
Attempt to clean up the Invoice data
pulls from main combined sheet
divides into sheets for each PI
exports each PI sheet as a PDF
current version you need to delete any sheets
plan to add a check and delete sheets if they already exist
you also need to add any new sheets to the hiding of sheets prior to pdf print
make sure to hide sheets prior to running 'Save sheets to PDF' if you don't want to print all the extra sheets
make sure to rename or delete the old BUInternalInvoice prior to running 'Generate BU Interanal Invoice'
*/

// By default, PDFs are saved in your Drive Root folder
// To save in the same folder as the spreadsheet, change the value to 'false' without the single quote pair
// You must have EDIT permission to the same folder
var saveToRootFolder = false

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Generate BU Interanal Invoice', 'BUInternalInvoice')
    .addItem('Split and Clean Invoice Combined data', 'createSheets')
    .addItem('Hide non PI Sheets', 'HideSheets')
    .addItem('Save sheets to PDF', 'SaveAsPDF')
    .addItem('Show hidden sheets', 'ShowHidden')
    .addToUi()
}

function BUInternalInvoice() {
  var ss = SpreadsheetApp.getActive();
  var sourcess = ss.getSheetByName("Combined");
  var ws = ss.insertSheet();
  ss.getRange('A1').activate();
  ss.getCurrentCell().setValue('Invoice Month');
  ss.getRange('A2').activate();
  ss.getCurrentCell().setFormula('=FILTER(Combined!A1:A,Combined!$G:$G="Boston University")');
  aVals = ws.getRange("A1:A").getValues();
  aLast = aVals.filter(String).length+1;
  ss.getRange('B1').activate();
  ss.getCurrentCell().setValue('Manager (PI)');
  ss.getRange('B2').activate();
  ss.getCurrentCell().setFormula('=FILTER(Combined!D1:D,Combined!$G:$G="Boston University")');
  ss.getRange('D1').activate();
  ss.getCurrentCell().setValue('DeleteMe');
  ss.getRange('D2').activate();
  ss.getCurrentCell().setFormula('=FILTER(Combined!B1:B,Combined!$G:$G="Boston University")');
  ss.getRange('C1').activate();
  ss.getCurrentCell().setValue('Project');
  ss.getRange('C2').activate();
  ss.getCurrentCell().setFormula('=REGEXEXTRACT(D2, "(.+)-")');
  ss.getRange('C2').activate();
  ss.getActiveRange().autoFill(ss.getRange('C2:C'+(aLast-1)), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  ss.getRange('C2:C'+(aLast-1)).activate();
  var range = ss.getRange('C2:C'+(aLast-1));
  var values = range.getValues();
 // console.log(values);
  var add = ss.getRange('C2:C'+(aLast-1));
  add.setValues(values);
  ss.getRange('D:D').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange('A1:C'+(aLast-1)).activate();
  ss.getRange('A1:C'+(aLast-1)).copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  ss.getRange('A:C').activate();
  ss.getActiveRange().removeDuplicates([2, 3]).activate();
  ss.getRange('D1').activate();
  ss.getCurrentCell().setValue('Cost');
  ss.getRange('D2').activate();
  ss.getCurrentCell().setFormula('=SUMIF(Combined!$B:$B,"*"&$C2&"*",Combined!L:L)');
  ss.getRange('E1').activate();
  ss.getCurrentCell().setValue('Credit');
  ss.getRange('E2').activate();
  ss.getCurrentCell().setFormula('=SUMIF(Combined!$B:$B,"*"&$C2&"*",Combined!M:M)');
  ss.getRange('F1').activate();
  ss.getCurrentCell().setValue('Subsity');
  ss.getRange('F2').activate();
  ss.getCurrentCell().setFormula('=IF(SUMIF(Combined!$D:$D,"*"&$B2&"*",Combined!M:M)=SUMIF(Combined!$D:$D,"*"&$B2&"*",Combined!L:L),,IF(IF(COUNTIF($B$2:$B2,$B2)=1,100,)>SUMIF($B$2:$B,B2,$D$2:$D),SUMIF($B$2:$B,B2,$D$2:$D),IF(COUNTIF($B$2:$B2,$B2)=1,100,)))');
  ss.getRange('G1').activate();
  ss.getCurrentCell().setValue('Balance');
  ss.getRange('G2').activate();
  ss.getCurrentCell().setFormula('=D2-E2-F2');
  ss.getRange('D2:G2').activate();
  bVals = ws.getRange("B1:B").getValues();
  bLast = bVals.filter(String).length+1;
  ss.getActiveRange().autoFill(ss.getRange('D2:G'+(bLast-1)), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  ss.getRange('A1').activate();
  var currentCell = ss.getCurrentCell();
  ss.getActiveRange().getDataRegion().activate();
  currentCell.activateAsCurrentCell();
  ss.getRange('A1:G'+(bLast-1)).applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);
  var banding = ss.getRange('A1:G'+(bLast-1)).getBandings()[0];
  banding.setHeaderRowColor('#4dd0e1')
  .setFirstRowColor('#ffffff')
  .setSecondRowColor('#e0f7fa')
  .setFooterRowColor(null);
  ss.getRange('B1').activate();
  ss.getActiveSheet().autoResizeColumns(2, 1);
  ss.getActiveSheet().autoResizeColumns(3, 1);
  ss.getActiveSheet().autoResizeColumns(4, 1);
  ss.getActiveSheet().autoResizeColumns(5, 1);
  ss.getActiveSheet().autoResizeColumns(6, 1);
  ss.getActiveSheet().autoResizeColumns(7, 1);
  ss.getActiveSheet().autoResizeColumns(1, 1);
  ss.getRange('I:Y').activate();
  ss.getActiveSheet().deleteColumns(ss.getActiveRange().getColumn(), ss.getActiveRange().getNumColumns());
  ss.getRange(bLast+':1000').activate();
  ss.getActiveSheet().deleteRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());
  ss.getRange('D:G').activate();
  ss.getActiveRangeList().setNumberFormat('0.00');
  ws.setName("BUInternalInvoice");
};

function createSheets() 
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceWS = ss.getSheetByName("Combined");

  const pi = sourceWS
    .getRange(2, 4, sourceWS.getLastRow()-1,1)
    .getValues()
    .map(pia => pia[0]);

  const uniquepi = [ ...new Set(pi)];
  const currentSheetNames = ss.getSheets().map(s => s.getName());

  let ws;
  let Inst;
  let PI;
  let DateT;
  let combined;
  let aVals;
  let aLast;

  uniquepi.forEach(pip =>
  {
    if(!currentSheetNames.includes(pip))
    {
      ws = null;
      ws = ss.insertSheet();

      ws.getRange("A2").setFormula(`=FILTER('Combined'!A2:O,'Combined'!D2:D="${pip}")`);
      sourceWS.getRange("A1:O1").copyTo(ws.getRange("A1:O1"));
      Inst = ws.getRange("G2").getValue();
      PI = ws.getRange("D2").getValue();
      DateT = ws.getRange("A2").getValue();
      combined = Inst+"_"+PI+"_"+DateT;
      ws.setName(combined);
      aVals = ws.getRange("A1:A").getValues();
      aLast = aVals.filter(String).length+1;
      clearFormatting();
      ws.getRange("K"+aLast).setValue('Total').setFontWeight("bold");
      ws.getRange("L"+aLast).setValue(`=ArrayFormula(SUM(ROUND(L2:L${aLast-1},2)))`).setFontWeight("bold");
      ws.getRange("M"+aLast).setValue(`=ArrayFormula(SUM(ROUND(M2:M${aLast-1},2)))`).setFontWeight("bold");
      ws.getRange("O"+aLast).setValue(`=ArrayFormula(SUM(ROUND(O2:O${aLast-1},2)))`).setFontWeight("bold");
      ss.getRange('A1:O'+(aLast-1)).applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY);
      banding = ss.getRange('A1:O'+(aLast-1)).getBandings()[0];
      banding.setHeaderRowColor('#4dd0e1')
      .setFirstRowColor('#ffffff')
      .setSecondRowColor('#e0f7fa')
      .setFooterRowColor(null);
      ss.getRange('1:1').activate();
      ss.getActiveRangeList().setFontWeight('bold');
      ss.getActiveRangeList().setHorizontalAlignment('center');
      ss.getActiveRangeList().setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
      ss.getActiveSheet().setRowHeight(1, 38);
      ss.getActiveSheet().setColumnWidth(1, 58);
      ss.getActiveSheet().autoResizeColumns(2,3);
      ss.getActiveSheet().setColumnWidth(5, 54);
      ss.getActiveSheet().setColumnWidth(6, 60);
      ss.getActiveSheet().setColumnWidth(7, 72);
      ss.getActiveSheet().setColumnWidth(8, 96);
      ss.getActiveSheet().setColumnWidth(9, 110);
      ss.getActiveSheet().autoResizeColumns(10,3);
      ss.getActiveSheet().setColumnWidth(13, 45);
      ss.getActiveSheet().setColumnWidth(14, 48);
      ss.getActiveSheet().setColumnWidth(15, 58);
      //Utilities.sleep(10000)
    }
    //exportCurrentSheetAsPDF();
  });
}

function SaveAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();

  allSheets.forEach(function(sheet){
    if(!sheet.isSheetHidden()){
      var sheetId = sheet.getSheetId(); 
      var url = "https://docs.google.com/a/mydomain.org/spreadsheets/d/" + ss.getId() + "/export?exportFormat=pdf&gid=" + sheetId + "&access_token=" + ScriptApp.getOAuthToken();

      // Save PDF to Google Drive so you don't have to do it manually later
      var folderSave = DriveApp.getFolderById('1UtWFOAIaBpct_rZc1UF8dYafphV-xTvp'); // make this where you want to save it.
      var blob = UrlFetchApp.fetch(url).getBlob().setName(sheet.getName() + ".pdf");
      // this should make the file 
      folderSave.createFile(blob);
      Utilities.sleep(8000);
    }
  });
}

function ShowHidden(sheetName) {
  SpreadsheetApp.getActive().getSheets().forEach(sh=>sh.showSheet());
}

function HideSheets(sheetName) {
  hideSheet("Combined");
  hideSheet("Lenovo Total SU");
  hideSheet("Lenovo");
  hideSheet("BUProjectContacts");
  hideSheet("BUInternalInvoice");
  hideSheet("HU&BU");
  hideSheet("Non-Billable");
  hideSheet("Institutional RollUp");
}

function clearFormatting() {
  var activeSheet = SpreadsheetApp.getActiveSheet();

  activeSheet.clear({
    formatOnly: true,
    contentsOnly: false
  });
}

function hideSheet(sheetName) {
 SpreadsheetApp.getActive().getSheetByName(sheetName).hideSheet();
}
joachimweyl commented 1 month ago

reopening to track updating previous months sheets and PDFs