Office Scripts help Analysis Cloud Limited automate and modernize workflow

Office Add-ins team

June 24th, 2021 0

Learn how Office Scripts in Excel helped Analysis Cloud Limited modernize their workflow with Leslie Black, Developer, Analysis Cloud Limited.

Office Scripts reduce an arduous process to a simple action

At Analysis Cloud Limited, we used Office Scripts in Excel to completely automate a Covid-19 reporting process used by one of our clients. Using scripts, we reduced the 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 third-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 a 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 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, we were 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 Office Scripts 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.')