General Discussion

  • 1.  Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts

    Posted 04-18-2024 16:02
      |   view attached

    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 repeatable 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 Automate. (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.  

    Using Power 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
    ------------------------------

    Attachment(s)

    xlsx
    template.xlsx   14 KB 1 version


  • 2.  RE: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts

    Adopter
    Posted 04-19-2024 02:32

    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
    ------------------------------



  • 3.  RE: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts

    Posted 04-19-2024 08:39

    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


  • 4.  RE: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts

    Adopter
    Posted 05-05-2024 00:01

    Hi Ian 

    Is there a way to use a similar approach to format images that are inserted into an excel template from the forms, specifically signatures?

    I would like the signatures inserted in our template to be bolder and placed in the cells, rather than on the cell. 

    If they are not placed inside the cell, they do not align with the center and they overlay the cell borders. 

    I can do this manually and make the signature bolder by applying an 'artistic' effect to the image, however, would you have any suggestions to achieve these manipulations without manual intervention. 

    Current Form Output:

    Desired Effects:

    Side note: it would be great if the signature images could be automatically sized to match the extents of the signature drawn, as our field techs often don't use the entire screen, resulting in small signatures.

    Thanks 

    Alex 



    ------------------------------
    Alex Sim
    Project Engineer
    Monadelphous
    ------------------------------



  • 5.  RE: Tech Talk - Implementation: Augmenting your Excel Template with Office Scripts

    Posted 05-06-2024 10:49

    Hello Alex,

    The image that is produced from a signature is pretty static from our end in terms of background transparency and size. From what I have seen there is no way with an Office Script to change the transparency of the background of an image either.

    Using an Office Script, you might be able to specify in the cell vs on the cell but not sure that will help because as you mentioned, with the big background the actually signature will be really small when goes in a cell.

    Thank you



    ------------------------------
    Ian Chamberlain
    Solutions Architect
    TrueContext
    ------------------------------

    Original Message


Reminder: Content posted to our Community is public content.  Please be careful not to post Intellectual Property that you do not have permission to share.  For more information please refer to our Terms Of Use.