Analysis Cloud Limited automates Excel reporting, modernizes workflow using Office Scripts

Leslie Black, Developer, Analysis Cloud Limited

Reducing an arduous process to a simple action 

At Analysis Cloud Limited, we’ve used Office Scripts to completely automate a Covid-19 reporting process used by one of our clients. Using Office Scripts, we reduced an arduous process to a simple action.  

Before using Office Scripts, our client needed to extend chart ranges and add new data points by hand, gather data from contributor’s emails, and even manually find pictures of charts from 3rd party websites, as well as gather data from contributor’s emails and enter them manually into a workbook. Now they can just feed their daily data into Lists and Teams and Power Automate sends this data to an Office Script that creates their report. Our script, also automatically updates a Power BI report, creates a daily PDF and creates backups to Azure Blob Storage.  

Using Office Scripts was a huge change in productivity for our client. The process of updating their Covid report took one employee all day, every day to manage – and is now a completely automated activity, freeing up time and energy for our clients to do more with their day!  

A learning opportunity for our business 

Additionally, this was a great learning opportunity for our business. Previously we have helped many clients using VBA. In this case, Office Scripts was able to replicate what we might have done with VBA and brought the extra benefit of relocating Excel workbooks to the cloud.  

Interested in how we did this? Check out this sample similar to part of our code:  

function main(workbook: ExcelScript.Workbook) { 

  let sheet = workbook.getWorksheets()[0] 

  let table2 = workbook.getTable("Table2") 

  // Delete 1 row(s) at index 0 from table Table2 

  let Table2RowCount = table2.getRowCount() 

  if(Table2RowCount==0){ 

    console.log('Table2RowCount: ' +Table2RowCount) 

  }else{ 

    console.log(Table2RowCount +' rows deleted.') 

    table2.deleteRowsAt(0, Table2RowCount); //deletes Table2RowCount (x) rows 

  } 

  //delete last column 

  let tableRangeCC = table2.getRange() 

  let tableRangeCCAddress = tableRangeCC.getAddress() 

  console.log(tableRangeCC.getAddress())  //Sheet1!C4:F5 

  //obtain value of F4 

  let searchColon = tableRangeCCAddress.search(":") 

  let firstRow = tableRangeCCAddress.substring(searchColon - 1,searchColon) 

  let lastCol = tableRangeCCAddress.substring(searchColon +1, searchColon+2) 

  let lastHeaderVal = sheet.getRange(lastCol + firstRow).getValue().toString() 

  

  console.log('lastColName: ' +lastHeaderVal) //// 

  if(lastHeaderVal =='Week'){ 

    table2.getColumns()[3].delete() 

    console.log('Column ' + lastHeaderVal + ' was deleted.')

Want to try Office Scripts for yourself? Create an Office Script and learn the basics with our tutorial here.

0 comments

Comments are closed. Login to edit/delete your existing comments