Hey Alicia,
That is an awesome use case for this, would you mind sharing how you are handling that?
Are you sending the signature to a folder in OneDrive and then using the Office Script to pick it up from there and put it in your spreadsheet? I love hearing about the creative ways that our customers are solving problems like that!
Thank you
------------------------------
Ian Chamberlain
Solutions Architect
TrueContext
------------------------------
Original Message:
Sent: 04-19-2024 02:32
From: Alicia Rico
Subject: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts
Hello Ian,
I'm already using this process in a workflow to insert signatures from a Customer Feedback form in an exel generated by a normal Form.
It works great!
Alicia
------------------------------
Alicia Rico
CI
Johnson Controls
Original Message:
Sent: 04-18-2024 16:02
From: Ian Chamberlain
Subject: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts
In this post, we are going to demonstrate how we can perform some automated tasks to an Excel Template Document that is generated from TrueContext, specifically, we will be dynamically renaming the tabs in a Workbook based on the data that is in each of the sheets. These scripts can be very powerful, and this example is just scratching the surface on how to get started.
Our Excel Template documents allow you to map out, cell by cell, which answer you want to put into which cell. This is a great way to quickly and easily get the look and feel of an Excel document that you might have already built out and is being used in your business. Office Scripts are stored in your SharePoint/OneDrive folder and are written in TypeScript (don't worry ChatGPT knows how to write in TypeScript if you are not familiar) and the best part is that you can use PowerAutomate to trigger a script to run against an Excel document based on a trigger.
In this example, I will use the Building Inspection form found in the Forms Library. I have mapped out some of the answers from the form to span across a couple tabs in the workbook, which will have 1 floor per tab, and I would like to rename the tab based on the floor that is written to that tab.
Here is the very simple Excel Template that I am using for this example that is pulling answers from a nested able section and showing information about each floor on separate tabs, this is the first row of the Floors to Inspect repeat section. I have also attached the template to the post so you can see the whole thing.
You will notice that the tabs along the bottom do not have very user-friendly names and I would like to rename each of the tabs based on what is in cell A1, for this, we can use tools that Microsoft provides called Office Script and Power . (There are a lot of resources online about all the different use cases for Power Automate)
Open Excel or Excel Online, navigate to the Automate tab, and click on New Script. Delete the script that is in there and paste in the below and Save Script. Office Scripts are automatically saved in your OneDrive folder under Documents/Office Scripts by default.
function main(workbook: ExcelScript.Workbook) {
// Get all sheets in the workbook
let sheets = workbook.getWorksheets();
// Loop through each sheet
for (let sheet of sheets) {
// Get the value from a specific cell (e.g., B2) for updating sheet name
let newName = sheet.getRange("A1").getText();
// Update the sheet name if the cell value is not empty
if (newName && newName.trim() !== "") {
sheet.setName(newName);
}
}
}
You can then use this script across any Excel workbook that you have by opening the Automate tab and you will see your new script that we just created. You can try to run it against any sheet and see that it will automatically rename the tab names.
Automate, we can trigger the new script to run anytime there is a new spreadsheet that gets added to your OneDrive or Sharepoint. Go to Power Automate and create a new flow with the trigger set to "When a file is created" and point it to the folder that you would like to watch. Next you will add an action after your trigger, and this action will be "Run script from SharePoint Library" using the File Identifier from the trigger and pointing to our new script that we just created in the /Documents/Office Scripts folder in your OneDrive.
It should look something like this:
Now every time a spreadsheet is added to the folder that you are watching, the script will get triggered and rename all the tabs according to the value that is in cell A1.
This is really just a simple example of what you are able to do using these types of scripts, so some of the steps above may change based on your set up and you can tweak the script to do different tasks for you.
Can you think of any other uses for this functionality that would help you automate more actions in your Excel Templates?
#TechTalkImplementation #Microsoft #PowerAutomate
------------------------------
Ian Chamberlain
Solutions Architect
TrueContext
------------------------------