![\"\"](\"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-content\/uploads\/sites\/73\/2021\/06\/LeslieBlack-150x150.png\")
<\/p>\n<\/p>\n
Learn how Office Scripts in Excel helped Analysis Cloud Limited modernize their workflow with Leslie Black, Developer, Analysis Cloud Limited.<\/p>\n
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. <\/span>\u00a0<\/span><\/p>\n Before using Office Scripts, our client needed to extend chart ranges and add new data points by hand, gather data from contributor\u2019s emails, and even manually find pictures of charts from\u00a0<\/span>third-party websites, as well as gather data from contributor\u2019s 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. <\/span>\u00a0<\/span><\/p>\n 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 \u2013 and is now a completely automated activity, freeing up time and energy for our clients to do more with their day! <\/span>\u00a0<\/span><\/p>\n 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. <\/span>\u00a0<\/span><\/p>\n Interested in how we did this? Check out this Office Scripts sample similar to part of our code: <\/span>\u00a0<\/span><\/p>\n Record, edit, and create Office Scripts in Excel on the web<\/a><\/span><\/span><\/p>\nA learning opportunity for our business<\/h3>\n
function main(workbook: ExcelScript.Workbook) { \r\n\r\n let sheet = workbook.getWorksheets()[0] \r\n\r\n let table2 = workbook.getTable(\"Table2\") \r\n\r\n \/\/ Delete 1 row(s) at index 0 from table Table2 \r\n\r\n let Table2RowCount = table2.getRowCount() \r\n\r\n if(Table2RowCount==0){ \r\n\r\n console.log('Table2RowCount: ' +Table2RowCount) \r\n\r\n }else{ \r\n\r\n console.log(Table2RowCount +' rows deleted.') \r\n\r\n table2.deleteRowsAt(0, Table2RowCount); \/\/deletes Table2RowCount (x) rows \r\n\r\n } \r\n\r\n \/\/delete last column \r\n\r\n let tableRangeCC = table2.getRange() \r\n\r\n let tableRangeCCAddress = tableRangeCC.getAddress() \r\n\r\n console.log(tableRangeCC.getAddress()) \/\/Sheet1!C4:F5 \r\n\r\n \/\/obtain value of F4 \r\n\r\n let searchColon = tableRangeCCAddress.search(\":\") \r\n\r\n let firstRow = tableRangeCCAddress.substring(searchColon - 1,searchColon) \r\n\r\n let lastCol = tableRangeCCAddress.substring(searchColon +1, searchColon+2) \r\n\r\n let lastHeaderVal = sheet.getRange(lastCol + firstRow).getValue().toString() \r\n\r\n \r\n\r\n console.log('lastColName: ' +lastHeaderVal) \/\/\/\/ \r\n\r\n if(lastHeaderVal =='Week'){ \r\n\r\n table2.getColumns()[3].delete() \r\n\r\n console.log('Column ' + lastHeaderVal + ' was deleted.')<\/pre>\nResources<\/h3>\n