<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jean Paul Dosher on Medium]]></title>
        <description><![CDATA[Stories by Jean Paul Dosher on Medium]]></description>
        <link>https://medium.com/@jean.dosher?source=rss-cfc32b616f91------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*ANgjDk1p_i0dgJx0Nek7oA.jpeg</url>
            <title>Stories by Jean Paul Dosher on Medium</title>
            <link>https://medium.com/@jean.dosher?source=rss-cfc32b616f91------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 19:50:24 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jean.dosher/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[YAML in Power Apps (part 2): Refactoring Power Apps Canvas App Control Names]]></title>
            <link>https://medium.com/@jean.dosher/yaml-in-power-apps-part-2-refactoring-power-apps-canvas-app-control-names-1fad3b8cae61?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/1fad3b8cae61</guid>
            <category><![CDATA[powerapps]]></category>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[yaml]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Tue, 17 Sep 2024 22:21:05 GMT</pubDate>
            <atom:updated>2024-10-24T12:00:42.235Z</atom:updated>
            <content:encoded><![CDATA[<p>This post explores a simple application I’ve developed to streamline control name refactoring within Power Apps Canvas Apps code that save a bunch of time renaming duplicated controls in Power Apps Studio</p><h3>🤦‍♂️ Lots of Controls to rename‍</h3><p>Recently I got into a project were a “legacy” Canvas Power Apps. I appropriated the term here, but just to point out that the maturity of development of the app was low and don’t used any least good practices. No kidding, in some cases we got a control replicated n times, just like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/283/1*oTpWwCtJqudJVF0AXlWImA.png" /><figcaption>Reproduction of replicated control</figcaption></figure><p>This turned into a huge problem in our refactoring/modernization task, even to discover the current APP.</p><p>So, as I was already excited about YAML serialization possibilities I notice that at this point a cool use of it will be awesome! ✍️(◔◡◔)</p><h3>🍲 Implementation</h3><h4><strong>Refactoring Goals and Logic:</strong></h4><p>The primary objective is to modify control names ending in the suffix “_2,_5,_100….” (e.g., Icon5_2). The script aims to achieve this by adhering to the following naming convention:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*tt7ohcOfdGrccR1m-UsFuw.png" /><figcaption>Standard desirable after Refactoring</figcaption></figure><p>The control type prefix, I’m following <a href="https://www.matthewdevaney.com/"><strong>Matthew Devaney</strong></a> “<a href="https://www.matthewdevaney.com/power-apps-coding-standards-for-canvas-apps/">2024 Power Apps Coding Standards For Canvas Apps</a>”</p><ul><li>{control type prefix}: This is derived from the control_prefixes dictionary (coding Standards).</li><li>{context/purpose}: Extracted from the control&#39;s Text or Tooltip property (priority given to Text).</li><li>{container/group}: Determined by the control&#39;s indentation level within the code.</li><li>{screen}: Retrieved from the first line of the YAML file (screen where the control is).</li></ul><p>Example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*UhUA0ioxMG0CAwfq89aznQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/724/1*cnHEodoh3tpa0dZDfkqBjg.png" /></figure><h4><strong>Understanding the Code Structure:</strong></h4><p>The script relies on a YAML file that defines the Power Apps Canvas App code schema. Additionally, I’ve established a dictionary named control_prefixes that maps control types (e.g., button) to their corresponding prefixes (e.g., btn).</p><p><strong>Key Script Steps:</strong></p><ol><li><strong>Line Reading and Control Information Extraction:</strong></li></ol><ul><li>The script reads each line of the YAML code.</li><li>It extracts the screen name and constructs the control hierarchy based on indentation levels.</li><li>It identifies controls with names ending that are eligible for refactoring (_2, _5,..._100…)</li><li>Control type (e.g., label) is determined.</li></ul><p>1.1 C<strong>ontext/Purpose Derivation:</strong></p><ul><li>The script prioritizes extracting context from the Text property.</li><li>If Text is unavailable, it attempts to use the Tooltip property</li></ul><p><strong>2. Refactoring and Renaming:</strong></p><ul><li>If the control name qualifies for refactoring, a new name is constructed using the defined format.</li><li>Both the original and new names are stored.</li></ul><p><strong>3. Reference Update:</strong></p><ul><li>The script iterates through the code again, searching for references to the renamed controls (using the original names).</li><li>These references are updated to reflect the new control names.</li></ul><h4>👨‍💻The Code</h4><pre>#made by @jean-paul-dosher<br>#visit: https://medium.com/@jean.dosher<br><br>import re<br>from collections import defaultdict<br><br>def refactor_yaml(yaml_content):<br>    screen_name = get_screen_name(yaml_content)<br>    if screen_name is None:<br>        print(&quot;Unable to refactor: Screen name not found&quot;)<br>        return yaml_content  <br><br>    lines = yaml_content.split(&#39;\n&#39;)<br>    refactored_lines = []<br>    control_counters = defaultdict(int)  # for control name generation (if needed)<br>    control_name_map = {}  # to store old to new control name mapping<br><br>    for i, line in enumerate(lines):<br>        if re.search(r&#39;\w+_\d+ As \w+(\.\w+)?:&#39;, line):<br>            indentation = len(line) - len(line.lstrip())<br>            control_name, control_type = line.strip().split(&quot; As &quot;)<br>            control_type = control_type.split(&#39;.&#39;)[0]  # Remove any subtype after the dot<br><br>            # Only refactor if the control name ends with _\d+<br>            if re.search(r&#39;_\d+$&#39;, control_name) or &#39;_Unknown&#39; in control_name:<br>                container_name = get_container_name(indentation, lines, i)<br><br>                # Get control properties<br>                control_props = {}<br>                j = i + 1<br>                while j &lt; len(lines) and lines[j].startswith(&#39; &#39; * (indentation + 4)):<br>                    stripped_line = lines[j].strip()<br>                    if &#39;: &#39; in stripped_line:<br>                        prop, value = stripped_line.split(&quot;: &quot;, 1)<br>                        control_props[prop] = value<br>                    j += 1<br><br>                context_purpose = get_context_purpose(control_props)<br><br>                new_name = refactor_control_name(control_name, control_type, context_purpose, container_name, screen_name, control_counters)<br>                control_name_map[control_name] = new_name<br>                refactored_lines.append(line.replace(control_name, new_name))<br>            else:<br>                refactored_lines.append(line)<br>        else:<br>            # Check for references to other controls in properties<br>            for old_name, new_name in control_name_map.items():<br>                if old_name in line:<br>                    line = re.sub(r&#39;\b&#39; + re.escape(old_name) + r&#39;\b&#39;, new_name, line)<br>            refactored_lines.append(line)<br><br>    refactored_content = &#39;\n&#39;.join(refactored_lines)<br><br> <br>    return refactored_content<br><br># Updated get_screen_name function<br>def get_screen_name(yaml_content):<br>    # Pattern to match both quoted and unquoted screen names<br>    pattern = r&quot;(?:[&#39;\&quot;](.*?)[&#39;\&quot;]\s+As\s+screen|(\w+)\s+As\s+screen)&quot;<br>    match = re.search(pattern, yaml_content)<br>    if match:<br>        # The screen name will be in either group 1 or group 2<br>        screen_name = match.group(1) or match.group(2)<br>        print(f&quot;Screen name found: {screen_name}&quot;)<br>        return screen_name<br>    else:<br>        print(&quot;Screen name not found in YAML content&quot;)<br>        return None<br><br>def get_container_name(indentation, lines, current_index):<br>    for i in range(current_index - 1, -1, -1):<br>        line = lines[i].strip()<br>        if &quot; As &quot; in line and any(container_type in line for container_type in [&quot;group&quot;, &quot;groupContainer&quot;, &quot;container&quot;]):<br>            return line.split(&quot; As &quot;)[0]<br>    return &quot;Unknown&quot;<br><br>def get_context_purpose(control_props):<br>    return control_props.get(&quot;Icon&quot;, control_props.get(&quot;Text&quot;, control_props.get(&quot;Tooltip&quot;, &quot;Unknown&quot;)))<br><br>def refactor_control_name(old_name, control_type, context_purpose, container_name, screen_name, control_counters):<br>    control_type_prefix = control_type.lower()[:3]<br>    context = re.sub(r&#39;[^a-zA-Z0-9]&#39;, &#39;&#39;, context_purpose)[:10]<br>    control_counters[control_type] += 1<br>    return f&quot;{control_type_prefix}_{context}_{container_name}_{screen_name}_{control_counters[control_type]}&quot;<br><br># Main execution<br>if __name__ == &quot;__main__&quot;:<br>    # Ask the user for the input filename<br>    input_filename = input(&quot;Enter the name of the YAML file to refactor: &quot;)<br><br>    # Read the YAML file<br>    try:<br>        with open(input_filename, &#39;r&#39;, encoding=&#39;utf-8&#39;) as file:<br>            yaml_content = file.read()<br>    except FileNotFoundError:<br>        print(f&quot;Error: The file &#39;{input_filename}&#39; was not found.&quot;)<br>        exit(1)<br>    except IOError:<br>        print(f&quot;Error: Unable to read the file &#39;{input_filename}&#39;.&quot;)<br>        exit(1)<br><br>    # Refactor the YAML content<br>    refactored_yaml = refactor_yaml(yaml_content)<br><br>    # Write the refactored YAML to a new file<br>    output_filename = f&quot;refactored_{input_filename}&quot;<br>    with open(output_filename, &#39;w&#39;, encoding=&#39;utf-8&#39;) as file:<br>        file.write(refactored_yaml)<br><br>    print(f&quot;Refactoring complete. Check &#39;{output_filename}&#39; for the result.&quot;)</pre><h3>🤓 Usage</h3><p>For now, the usage is for mid-level users:</p><p>1. The user must follow the <a href="https://medium.com/@jean.dosher/yaml-in-power-app-canvas-5d97cc6790bb">unpack/pack process</a> mentioned in last article to be able to unpack the <em>msapp </em>(<em>through CLI canvas pack/unpack</em>) and copy the screen YAML file from <em>Src </em>directory</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/320/1*J-fG8BikzXaq1oA7TwPRcA.png" /></figure><p>2. In application directory, paste the copied YAML file and run the script:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/208/1*bfNCuBFE_SzDnYxfCwJlaA.png" /><figcaption>Refactor directory of the containing the executable file</figcaption></figure><p>As output, we get refactored_{filename}.yaml. Now just copy this into <em>Src </em>directory of unpacked APP, exclude the “old” file and rename the refactored file</p><p>3. Pack the APP with PAC canvas pack</p><p>4. I really don’t know if there is another way to do this, but to open the <em>msapp </em>in my environment (without importing as a zip) is opening any APP in <em>Power Apps Studio</em> and open the refactored <em>msapp</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S7FOmQ2Rm2qTGE-JSkGhoQ.png" /><figcaption>Import file msapp file</figcaption></figure><h4>🚑 Bumps in the road</h4><blockquote>Now, i need to put some testimonials here about the process of developing this application.</blockquote><p>As a concept I intended that the application to be user friendly, with a user interface, etc.…<br>So, I’ve used <a href="https://cursor.com/">Cursor AI </a>code editor to transcript the code to C# and wrap it in a bigger solution with <em>Windows Forms</em> with every beauty charming UI, but got stuck trying to wrap the pack/unpack process into solution.</p><p>I got even more excited knowing some amazing tools in <a href="https://github.com/microsoft/PowerApps-Tooling">Power Apps Tooling repo</a>, one these tools just seem to fit perfectly into my problem, that is the <a href="https://github.com/microsoft/PowerApps-Tooling/tree/master/src/PASopa"><em>Legacy Source File Pack and Unpack Utility (PASopa)</em></a><em>. </em>I ended up ruining everything and decided to keep it simple for me and my team usage</p><h3><strong>🏁 Conclusion</strong></h3><p>Well, after all, I solved this huge problem that i have and maybe in the near future we can improve it to refactor some other cases. <br>For now, a container of a group is suggested for the naming and if there is absolutely no context the renaming still serves it proposes giving the minimal refactored name possible (the control type and screen name at the end). Even using LLM’s sometimes is impossible to get some context of the Control (trust me, I tried)</p><p>🐞Known Bugs:<br>- Screen Names with a space add a ‘ to the refactored controls, and this breaks when trying to pack to .msapp</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1fad3b8cae61" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[YAML in Power App Canvas]]></title>
            <link>https://medium.com/@jean.dosher/yaml-in-power-app-canvas-5d97cc6790bb?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d97cc6790bb</guid>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[low-code]]></category>
            <category><![CDATA[powerapps]]></category>
            <category><![CDATA[powerplatform]]></category>
            <category><![CDATA[yaml]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Tue, 09 Jul 2024 20:19:47 GMT</pubDate>
            <atom:updated>2024-07-20T11:31:16.012Z</atom:updated>
            <content:encoded><![CDATA[<p>What is useful and practical in the new YAML feature in Canvas Apps? And how could impact an entire development process.</p><h3>Introduction</h3><p>Some contents about YAML have already been released, but only the code view feature in Canvas Apps Studio is not quite a practical feature. There’s a lot more going on, I would like to share with you some insights, possibilities and “in-code” details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/1*laUQftULeRA-FSb7BT1PhA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*1uKI4GjsN4Py_NyjbUWqzg.png" /><figcaption>Example of Code View in Canvas App</figcaption></figure><h4>What is YAML?</h4><p>YAML will act as an abstraction layer between the very low-code (that is personified as the App Studio) and the core of the application, you can’t open a downloaded “yourApp.msapp” app, but with YAML you’ll get it delivered and abstracted.</p><p>So, YAML is a data Serialization Language, it’s human readable (way more readable than JSON and XML) &amp; computationally powerful. Usually works as configuration file (with keys and values)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1Sro2A4uVMdkw_qSA7HPVA.png" /><figcaption>Speculated Diagram of the multi tools and structures behind Canvas APP</figcaption></figure><h3>🎗️Benefits of using YAML</h3><p>YAML can bring a variety of improvements to the developing process, in any kind of process maturity that your organization has.</p><h4>🏎Productivity</h4><ul><li>You can create Project Templates</li><li>Allows collaboration (with git)</li><li>Share and reuse code snippets 🔜 Create a snippet repository for that!</li><li>Opens up possibilities for automation, like bulk modifications</li><li>Best overview of the entire APP (i.e.: if you have a legacy app made by another developer and want to search for Power Automate calls in all the app screens)</li><li>You can ask questions to your app source code with GitHub copilot or another AI-based code assistant</li></ul><h4>🏆Quality</h4><ul><li>Force following coding best practices and design patterns for maintainable and scalable app development (with App Test Engine)</li><li>Implementing effective error handling and debugging techniques</li><li>Optimizing app performance</li></ul><h3>🧰Tools</h3><p>Enough talking. Now that we are familiar with its purposes and concepts lets dive in its tools and their utilities</p><h4>CLI</h4><p>The CLI(pac) comes into play to acts as a bridge unpacking a Canvas App into editable YAML files and repacking them after modifications.</p><p>The easiest and recommended method to use CLI(pac) is installing the Power Platform Tools extension for VS Code, it will automatically install the pac CLI and integrate it into the built-in VS Code terminal. This allows developers to use pac commands directly within VS Code</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/514/1*jeMvPBKg98sA-s_58uB8xA.png" /><figcaption>Power Platform Tools extension in VS Code</figcaption></figure><h4>Practice</h4><p>Before getting into the tools and their outcomes it is reasonable to expose a bit of the unpacked file and some details about file structure.</p><p><strong>Packing and unpacking a file:</strong></p><p>Within the downloaded app folder opened in VS Code, open the terminal and type:</p><pre>#to unpack<br>pac canvas unpack --msapp YourAppName.msapp --sources DestinyFolder<br><br>#to pack<br>pac canvas pack --msapp YourAppName.msapp --sources DestinyFolder</pre><p>And the folder structure will be like that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/310/1*ts-F3l7jhuiTXladRMXYNg.png" /><figcaption>Unpacked Folder</figcaption></figure><p>I recommend you to navigate all subfolders, it’s quite interesting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/578/1*K9t4LvixeM45t7oE2qyAGA.png" /><figcaption>Files Structures explained</figcaption></figure><p>All App screens will be allocated in “src” folder. An example of use that I had was searching for Power Automate Flows triggering in the VS Code search bar (with %.Run) -this look a real simple task, but trust me, isn&#39;t in Canvas App Studio</p><p><em>A tip:</em> also add the YAML extension to your VS Code:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/299/1*4MpERE0LGWZrtb_8uIi2eg.png" /><figcaption>YAML extension by Red Hat</figcaption></figure><p>You can add a YAML schema to enable <em>InelliSense (autocomplete)</em>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*zoTxsAlZ-gAsgxjJCHB7aQ.png" /></figure><p>For that, I recommend you to watch <a href="https://www.youtube.com/watch?v=fTMrS2y6Qew">Shane’s Young video</a> (12:33min). Nevertheless, if you are already familiar with schema configurations, here it is, just paste it into the <em>Settings &gt; Workspace &gt; Yaml: Schemas &gt; Edit in setting JSON:</em></p><pre>&quot;yaml.schemas&quot; : {<br>&quot;https://raw.githubusercontent.com/microsoft/PowerApps-Tooling/master/docs/pa.yaml-schema.json&quot;:<br>&quot;pa.yaml&quot;<br>}</pre><blockquote>I won’t go into details of the YAML structure in an app screen, you can give a try and also make some assumption making parallels with Canvas App Studio -yet, it&#39;s almost self-explanatory</blockquote><h4>Version Control</h4><p>Recently MS added Git version control enabling in Canvas App, with it, the development team can manage apps changes:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AJfzp5k6L4wv7FJDYz3uSQ.png" /><figcaption>Enabling Git version control</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*M3T7ELdmlXpudq3XvHG6XQ.png" /><figcaption>Example of a file comparation in Git Repo</figcaption></figure><h4>Test Engine</h4><p><a href="https://learn.microsoft.com/en-us/power-apps/developer/test-engine/overview">Test Engine</a> is an open-source project designed to empower citizen developers with a unified platform for automated testing of their canvas apps. Several key advantages:</p><ul><li><strong>Simplified Test Authoring with Power Fx:</strong> Leverage the familiar Power Fx language you already know to create tests within the user-friendly YAML format.</li><li><strong>Effortless DOM Abstraction:</strong> Focus on the app’s functionality, not the underlying code. Tests are written using references to control names defined during design time, eliminating the need for complex JavaScript or browser DOM manipulation.</li><li><strong>Enhanced Control with Connector Mocking:</strong> Simulate network calls without modifying your app! Connector mocking allows you to test app behavior in isolation, preventing unintended effects from external APIs during the testing process.</li><li><strong>Visualize Test Execution with Screenshots and Recordings:</strong> Gain valuable insights into test failures. The Power Apps Test Engine automatically captures screenshots and records videos throughout test execution, providing a clear visual record of the user experience during failed test cases.</li></ul><h3>🎢Roadmap</h3><p>It’s no secret that Microsoft wants to construct solid foundations for a full-stack ecosystem in Power Platform. With everything connected to the same basis, the semantics will be shared across the entire Power Platform, providing room for Copilot to fully understand business processes and apps “in toto.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MEoeCdryUEhpKkIBlq3Zhw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CCMZtyBF0dsDeid6rSGMSg.gif" /><figcaption>Microsoft Roadmap for “Pro-coding” Canvas Apps</figcaption></figure><p>We already have YAML implemented into Copilot Studio too!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wqVDEZ0zLi_99cevHsfTXQ.png" /><figcaption>YAML in Copilot Studio</figcaption></figure><h3>🏁Conclusion</h3><p>Having a more comprehensive overview of the possibilities will definitely make you think about your development process. Maybe one of the features will address a simple pain point, or you can see it as the next step to improve the development process —<em> or just a helper to understand your app.</em></p><p>The main goal of this article was to give a glimpse of all the benefits and tools (there is a lot to learn!). The near future is to use YAML to accelerate full-stack software development in Power Platform, improving substantially quality and productivity!</p><p>Feel free to contact me to discuss this article!</p><blockquote><strong><em>Just a reminder: most of these features are in preview, so using them in a production environment is not recommended.</em></strong></blockquote><h3>🔗References</h3><ul><li><a href="https://learn.microsoft.com/en-us/power-platform/developer/cli/reference/canvas">Pac CLI</a></li><li><a href="https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/git-version-control">Use Git version control to edit canvas apps (experimental)</a></li><li><a href="https://yaml.org/spec/1.2.2/">Oficial YAML documentation</a></li><li><a href="https://github.com/microsoft/PowerApps-Tooling/blob/master/docs/YAMLFileFormat.md">Legacy Power Apps YAML Source File Format</a></li><li><a href="https://github.com/microsoft/PowerApps-Tooling/tree/master/samples">Samples for creating, reading, and updating files containing Power Apps (*.msapp)</a></li><li><a href="https://github.com/microsoft/PowerApps-TestEngine">Power Apps Test Engine</a></li><li><a href="https://learn.microsoft.com/en-us/power-apps/developer/test-engine/yaml">Power Apps Test Engine YAML format (preview)</a></li><li><a href="https://www.youtube.com/watch?v=fTMrS2y6Qew">Shane Young Power Apps View Code and VS Code with YAML</a></li><li><a href="https://www.youtube.com/watch?v=rVMvAeZONPM&amp;t=0s">What the YAML?! Learn what it is and how it helps in Copilot Studio today!</a> — Audrie Gordon</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d97cc6790bb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SharePoint Premium -Content management evolution]]></title>
            <link>https://medium.com/@jean.dosher/sharepoint-premium-content-management-evolution-c301eedb455d?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/c301eedb455d</guid>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[sharepoint-development]]></category>
            <category><![CDATA[sharepoint-online]]></category>
            <category><![CDATA[sharepoint-premium]]></category>
            <category><![CDATA[content-management]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Sat, 23 Mar 2024 12:44:23 GMT</pubDate>
            <atom:updated>2024-03-23T13:09:35.631Z</atom:updated>
            <content:encoded><![CDATA[<p>Solidify your content management foundation to unlock the full potential of your intranet with artificial intelligence and advanced security and governance features.</p><h3>Introduction</h3><p>SharePoint Premium is an extended version of SharePoint, adding AI, automation, and intensified security capabilities for organizations with content management needs.</p><blockquote>The main idea here is to give value to your data, to your productivity, and obtain real insights from all of it.</blockquote><p>Here’s how AI, security, and governance come together to transform your content management:</p><p><strong>Boost Productivity &amp; Collaboration:</strong></p><ul><li>Collaborate on a wider range of files (hundreds of types!)</li><li>New document portal streamlines teamwork with external users.</li><li>AI helps prioritize documents needing your attention.</li><li>AI optimizes common business documents (invoices, contracts, proposals).</li></ul><p><strong>Smarter Content Organization with AI:</strong></p><ul><li>Improved file and video translation capabilities.</li><li>AI flags files containing sensitive information (PII).</li><li>Automatic redaction of sensitive information across SharePoint files.</li></ul><p><strong>Enhanced Content Governance for:</strong></p><ul><li>Identify overshared content and reduce sprawl.</li><li>IT admins can easily trigger site access reviews.</li><li>AI suggests access policy improvements.</li><li>Leverage Copilot for advanced content management (in near futere? perhaps).</li></ul><p><strong>Key Features:</strong></p><p>To support this, SharePoint Premium got great tools:</p><ul><li>Prebuilt document processing</li><li>Structured and freeform document processing</li><li>Unstructured document processing</li><li>Content assembly</li><li>Image tagging</li><li>Taxonomy tagging</li><li>Syntex eSignature</li><li>Optical character recognition</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t2LB4CIKJObZ0eAT6kCBCg.png" /><figcaption>Key features</figcaption></figure><h4>SharePoint vs SharePoint Premium vs Microsoft Syntex</h4><p>If you&#39;re asking yourself, where is Microsoft Syntex (As I did)? <br> — In November 2023, Microsoft rebranded Microsoft Syntex under the umbrella of SharePoint to streamline their offerings and better connect the AI-powered features to the core SharePoint platform.</p><p>And what&#39;s the difference between SharePoint and SharePoint Premium? <br>— Well, think of it (Premium) as a second or third step into a more sophisticated digital workspace. SharePoint Premium is designed for organizations that need more than just the basics, but choosing between them depends on your organization’s needs. If you require just basic file sharing and collaboration, SharePoint Standard might suffice. But for organizations dealing with large volumes of content or needing advanced automation and intelligence features, SharePoint Premium offers a significant upgrade.</p><h3>Conclusion</h3><p>As a developer, data analysis apprentice and former client of data products (aka manager role) I really feel that that data without context isn&#39;t information, it&#39;s just data. SharePoint Premium allows the leverage of content to support the foundation for Copilots. So, yes, it&#39;s really necessary and helpful.<br> The updates are nearly monthly, so Microsoft is investing a huge effort in it along with all their AI products.</p><h4><strong>Additional Resources</strong></h4><ul><li><a href="https://www.microsoft.com/en-us/microsoft-365/sharepoint/compare-sharepoint-plans">Official Microsoft SharePoint Premium website</a></li><li><a href="https://techcommunity.microsoft.com/t5/sharepoint-premium-blog/introducing-sharepoint-premium-the-future-of-ai-powered-content/ba-p/3981076">Microsoft blog about SharePoint Premium</a></li><li><a href="https://adoption.microsoft.com/en-us/case-studies/">SharePoint Premium case studies</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c301eedb455d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Big Brother Brazil (#BBB24) -Exploring TV show data with AI]]></title>
            <link>https://medium.com/@jean.dosher/big-brother-brazil-bbb24-6b396667d8df?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/6b396667d8df</guid>
            <category><![CDATA[rede-globo]]></category>
            <category><![CDATA[scraping]]></category>
            <category><![CDATA[bbb24]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[chatgpt]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Sun, 21 Jan 2024 12:23:45 GMT</pubDate>
            <atom:updated>2024-01-24T00:11:05.980Z</atom:updated>
            <content:encoded><![CDATA[<p>Exploring scraped data with OpenAI custom GPT&#39;s and code interpreter</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*W-OnT97vvHv2mE5L" /></figure><p>Revisiting my 2022 project with another perspective, now using the power of OpenAI&#39;s custom GPT&#39;s along with code interpreter to leverage the data analysis and some ideas to expand the entire system</p><h3>1. Context</h3><p>Back in 2022 I made a project to gather data from the oficial page of the famous Brazilian edition for the Big Brother TV show, at that time I was very focused in make things work (API&#39;s and scrapping) also basic understanding of graphs, make fun with my wife (she loves the Show) and of course, build a portfolio project.</p><p>I was struggling to decide to do this again, cause now is not my study objectives -but I must do something, it&#39;s fun to see the analysis results and discuss it later, and with gpt it&#39;s too easy</p><h3>2. Objectives</h3><p>As I mentioned, data science is not my actual working area and I will leave deep analysis (and the fun stuff) for those interested.</p><p>For now, the objective is:</p><ol><li>Do a schedule scraping in the official site to obtain valuable data (not that valuable 😅)</li><li>Build a custom GPT and make available in the Chat GPT Store</li><li>Make some data analysis with the Custom GPT</li><li>Propose an amplification of this system with Azure Functions, ChatGPT&#39;s action and Firebase Firestore</li></ol><h3>3. Acquiring data</h3><p>To acquire data; for concept proposes, I&#39;m still using <a href="https://colab.research.google.com/drive/1OH3EiEcaIH7q8qYdxE5kfm-WvxM4ydD_?usp=sharing">Google Colab&#39;s python notebook </a>(now also with a helpful AI assistant) empowered with Selenium library to scrap the Title and Content from posts in the official web page and mount a simple csv (for demonstration purpose). At the time of this article there&#39;s 1044 posts:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QAR027BU2Bq6R7V-jxnpyg.png" /></figure><p>Further, we will improve the scraping to run in a Cloud computing instance…</p><h3>4. Building a custom GPT</h3><p>Custom GPTs refer to specialized versions of the GPT models; tailored for specific applications or audiences. Imagine them as customized assistants, each trained to be exceptionally good in a particular domain or for a specific type of task. And now, there&#39;s a store for it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/0*lGy25re66ek3p4hC.jpeg" /><figcaption>GPT Store</figcaption></figure><p>Now, with a consistent database it is possible to add it to a custom GPT trained with that dataset.</p><p>To conceive the custom GTP and make it available to public the <strong>subscribe version is needed</strong> (ChatGPT PRO), the act of configuring is self-explained as a chat GPT prompt, where you answer targeted questions to narrow the instructions. In our case I gave some extra instructions too, this is the final instruction:</p><blockquote>‘BBB 24 — Data Analysis Show!’ is designed to engage users in Brazilian Portuguese with a casual and friendly approach, while focusing on data analysis related to ‘BBB 24’. It will personalize interactions by referencing previous data points from the show, and using familiar terms and phrases from ‘BBB 24’, making its responses feel more connected and relevant. This GPT will avoid discussing political issues and sexual content, maintaining a focus on data-driven insights. It will communicate complex data science in an accessible manner ensuring accuracy, while also clarifying uncertainties. The aim is to make data analysis enjoyable and relatable for a general audience, with a touch of personalization that resonates with fans of the show avoid technical jargon.</blockquote><p>After configuring the custom GPT, I provided the csv and also a names_list.csv just to adjust the participants names (and prevent errors)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wfHU1IyLq6LK-53MagjYDQ.png" /><figcaption>Knowledge and Capabilities</figcaption></figure><p>Good to go, here is a preview of the chat screen:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K8m9D9Ud9WtZJ6q45PkKAQ.png" /></figure><h3>5. Data analysis</h3><p>Well, time to test it. First, I revisited some old analysis, like the frequency of Participant mentions in posts and &quot;wordcloud&quot;. Also, I asked for suggestions and got the following prompt: &quot;Create visually appealing charts. Display funny statistics, charts, and graphs related to the show’s dynamics, participant behaviors, and audience reactions.&quot; -I confess that it seemed fun to me.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0_QcDRvNElfcC_7CRK6ooA.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jh4Q0jMPPouBftYw0a8lpg.jpeg" /><figcaption>Rooms/ambient mentions</figcaption></figure><p>For the suggestions, the charts are definitely wrong, but all they are good ideas, here is a glimpse:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ReHSdvEEuuKS_rZcoJ1z2g.jpeg" /><figcaption>Where and when did we have more conflicts?</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jQ-Ba_gzRDkHHJvqjvNRKw.jpeg" /></figure><h3>6. Graph Analysis</h3><p>Graph analysis is the most interesting, once again (as in 2022) I used it to social network analysis (SNA), where interactions between elements (vertices) create a relationship (edge). In this case, interactions between pairs in the posts were considered. It is also assumed that the interactions create ‘bonds’ between the two pairs, creating a symmetric network (If Alane is friends with Beatriz, Beatriz is friends with Alane).</p><blockquote>The interactions are very dense, since everyone interacts with everyone and it&#39;s a small group</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6BJ2F8SWw9wjxpLsc5SsyQ.png" /></figure><p>Improving a bit to better visualization and also to implement &quot;centralization degree&quot;; that measures the number of connections a node has, and centralization degree assesses how much this connectivity is concentrated in a few nodes within a network. For the graph the it&#39;s corresponding to the circles size</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VFx2mFah72L0DkWT5YnQUw.png" /></figure><p>Communities:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rLP3erK2XTNXP1gXdvAoOw.png" /></figure><p>Who is the participant with the most connections?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zcvBcOk2tDA2ULwfBdcrBA.png" /></figure><h3>7. Enhancing System Capabilities</h3><p>Firstly, I was excited to make this huge, but unfortunately, I need to focus on other learning tasks that currently are more align with my carrear path. But i notice at least one mention is appropriate.</p><h4>7.1 Automating and making it smarter</h4><p>Taking advantage of ideas of my 2022 article; to provide richer and public perceptions/point of view the system could use more external API&#39;s like (X/twitter ang Google Trends).</p><p>To automate the process, a python script runs scheduled in Azure Function (that is serverless) to update the datasets that are based in Firebase Firestone. With the database always updated, the custom GPT will consume the data using &quot;GPT actions&quot; and the user will always have updated information:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4uLQP2mU08TtGJv9oFMd0A.png" /><figcaption>System integration diagram</figcaption></figure><p>This is a system that I&#39;m currently capable of building, but there is room to enhance it more using transcription from recordings and go even further using machine learn to image processing (maybe someday 🤤)</p><h3>8. Conclusion</h3><p>The dataset is good, but it&#39;s from the official website, and this could be biased, although the custom GPT also does some web browsing that theoretically solves this, but its visible the limitations.</p><p>GPT&#39;s custom model still falling to analyse data, so be very careful with the data offered to the model, in my case it&#39;s visible the confusion with the names, even enforcing with a list a prompt adjusting.</p><p>However, code interpreter saves a lot of time when you are sketching and exploring the data, it&#39;s powerful. Not solid, but powerful!</p><p>It&#39;s important to emphasize that this is a small project and the main objective was to have some fun and update the 2022 analysis in the current season, for a business case an enterprise system is recommended (a real data science/machine learning project) and again, the possible analysis is infinity:</p><h4>8.1 Notes on the project:</h4><p>There are countless possibilities for social media analysis, especially in the field of marketing (relationship). It would also be advantageous in political campaigns. It would be fun to try to predict the winner of the edition!</p><ul><li>Use of some dataset from previous editions for comparison (maybe a “glossary” / “encyclopedia”?)</li><li>Analysis of transcription of dialogue from videos (perhaps from YouTube or official TV show).</li><li>Analyze program statistics. Collect/Analyze data in “key” periods (evictions, challenges, etc.).</li><li>Expand data collection sources to other social media platforms.</li></ul><h4>References, links and keywords</h4><ul><li><a href="https://gshow.globo.com/realities/bbb/">Oficial BBB page</a></li><li><a href="https://colab.research.google.com/drive/1OH3EiEcaIH7q8qYdxE5kfm-WvxM4ydD_?usp=sharing">Google Colab Notebook</a> of the scrapping using Selenium</li><li>My first data analysis with some &quot;juice&quot; in it, here a integrate with Google trends and twitter: <a href="https://medium.com/@jean.dosher/projeto-an%C3%A1lise-de-dados-do-bbb-22-fbf60c578b96">Análise de dados do Big Brother Brasil (#BBB 22) | by Jean Paul Dosher | Medium</a></li></ul><p>keywords: bbb24, customGPT, SNA, data analysis</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6b396667d8df" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Return data from office scripts and integrate systems with Power Automate]]></title>
            <link>https://medium.com/@jean.dosher/return-data-from-office-scripts-and-integrate-systems-with-power-automate-f74efbffb02c?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/f74efbffb02c</guid>
            <category><![CDATA[office-script]]></category>
            <category><![CDATA[power-platform-developers]]></category>
            <category><![CDATA[typescript]]></category>
            <category><![CDATA[power-automate]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Tue, 19 Dec 2023 22:57:42 GMT</pubDate>
            <atom:updated>2023-12-19T22:57:42.374Z</atom:updated>
            <content:encoded><![CDATA[<p>Business case of systems integration with Power Automate and a help of office scripts to make it error proof, effective and fancy.</p><h3>Context</h3><p>Let&#39;s suppose that you need to integrate a legacy system with your SharePoint List and the only source of data is an excel spreadsheet (😖). Power Automate handles this very well, but unfortunately there&#39;s room for issues of all kinds.</p><p>If the file doesn&#39;t have data structure consistency like columns out of order, data need to be formatted as a table or even wrong columns names. In all these cases (and there´s more), the automation execution breaks.</p><p>Another circumstance for this example is that we need to obtain certain data inside a cell, like an ID correlated to SharePoint item ID -in this condition, we will verify if the item already exists and if there’s an update in that item.</p><p>All these can be treated/manipulated with office scripts! In this article I’ll focus on a short office script explanation, solutions for the presented case and script’s integration with Power Automate.</p><blockquote>I’ll show a bit of the Flow’s Scopes, but there’s some handling to get more than 256 excel items of a spreadsheet and the mapping after that (maybe someday I’ll post the full solution), but for now, you will have to do your homework for that!</blockquote><p>Those are the SharePoint list that will receive data and the spreadsheet that will be the source (in this case without formatting, but there&#39;s a Flow action that break the table formatting)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ku_8YAkjNg3CYFkZqOLxXQ.png" /><figcaption>SharePoint List “Space Ships”</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*tDqPHQb3vBailA7KCpNjIg.png" /><figcaption>Spreadsheet of the import (without formatting)</figcaption></figure><h3>Office Scripts</h3><p>Office Scripts are a way to automate tasks in Excel. They allow you to record, edit, and perform custom automation actions in your spreadsheets. <a href="https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration?tabs=run-script">You can use Office Scripts to automate repetitive tasks, transform data, create reports, etc</a>. The scripts are written in TypeScript, a JavaScript-based programming language (you will need some familiarity with it) that adds features like static types and classes. You can use the code editor in Excel to create, edit, and run Office scripts. Basically, Office scripts can access and manipulate Excel objects such as workbooks, worksheets, tables, charts, and more.</p><h3>Implementation</h3><p>Let’s divide to conquer! Before any file manipulation, let Flow check if the file is in the correct expected extension (.xlsx), and then solve each issue with a separate action:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FJ3ARg6hRCGTfmH0v3_tMg.png" /><figcaption>Flow Scope with Spreadsheet handling</figcaption></figure><h4>Break table formatting and enforce its formatting</h4><p>Here is assumed that sometimes user makes mistakes and don&#39;t keep a pattern in table formatting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_qLXt1Qyc-HdR7yuTLpY4w.png" /><figcaption>ID column out of table range</figcaption></figure><p>For that, we&#39;ll use our first simple &quot;Run Script&quot; action:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*H25SNtMPwsp-0LYDSnrlbQ.png" /><figcaption>Run Script action</figcaption></figure><p>This function will check if there’s a table in the spreadsheet and convert it do range 0 (removing the table)</p><pre>function main(workbook: ExcelScript.Workbook) {<br><br>  //let selectedSheet = workbook.getActiveWorksheet();       <br><br>  let tabela = workbook.getTables()[0];<br><br>  // Check if the table is not empty<br>  if (tabela &amp;&amp; tabela.getRange().getValues().length &gt; 1) {<br>    tabela.convertToRange();<br><br>  }<br>}</pre><p>After cleaning the table formatting, you&#39;ll need to reformat it again using the native Excel Action in Power Automate called &quot;Create Table&quot; and obtain that table with &quot;Get tables action&quot; (to be used later in &quot;List rows&quot; action):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U3TqnO_9j6Z2La-uHBMBig.png" /><figcaption>“Create Table action” to enforce the expected table formatting</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1HVsb9MX1Hx4OngEohIFdw.png" /><figcaption>Get table action</figcaption></figure><h4>Get ID with Script</h4><p>Here&#39;s where the magic happens, in this case a better explanation is needed. Our source file will also be responsible for updating the corresponding item in the SharePoint List, here’s the trick; the item ID in the list (SharePoint) is in the Ship Description field -ok, you’re asking yourself why this is not in a separate column? The answer is, because of the legacy system that is generating the source file 😵!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/772/1*Iqw7Q3JJXhavXQYaAsem3g.png" /><figcaption>ID inside brackets in Ship Description</figcaption></figure><p>With Power Automate expressions you can try to manipulate the string to get what&#39;s inside the bracket, but if there are two pairs of brackets in random positions? I&#39;m sure you can write a huge expression to handle that, but if need to debug it?</p><p>The smartest and secure way is using office script with <em>regex</em>, this one is a bit handy:</p><h4>Regex</h4><blockquote>Regex, short for regular expression, is a sequence of characters that defines a search pattern for text. Regex can be used to find, replace, or validate text based on some rules.</blockquote><p>Regex is a subject that requires an article itself, but fortunately chat GPT can be extremely helpful writing regex expressions!</p><p>Implementation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*49WrJ5m1IrzI-rPGBJSCKA.png" /><figcaption>Action to call Get_ID-inside_bracket script</figcaption></figure><p>script:</p><pre>function main(workbook: ExcelScript.Workbook) {<br><br>    // Get active Worksheet<br>    let currentWorksheet = workbook.getActiveWorksheet();<br>  <br>    // Get used range <br>    let interval = currentWorksheet.getUsedRange().getLastRow().getRowIndex() + 1;<br>  <br>    const definedRange = `I2:I${interval}`;<br>    const definedRangeID = `J2:J${interval}`;<br>  <br>    // Get range from the defined range.<br>    let range = currentWorksheet.getRange(definedRange);<br>    let rangeID = currentWorksheet.getRange(definedRangeID);<br>  <br>    // Get the size boundaries of the range.<br>    let rows = range.getRowCount();<br>    let cols = range.getColumnCount();<br>  <br>    // Regex for 3 cases [1], [ 1], [1 ]<br>    let regex = /\[(\d+)\]/;<br>    let regexFinal = new RegExp(&quot;\\[(\\d+ )\\]&quot;, &quot;g&quot;);<br>    let regexFront = new RegExp(&quot;\\[(\\s\\d+)\\]&quot;, &quot;g&quot;);<br>  <br>    // Iterate over the range.<br>    for (let row = 0; row &lt; rows; row++) {<br>      for (let col = 0; col &lt; cols; col++) {<br>  <br>        let cellValue = range.getCell(row, col).getValue();<br>        let cellValueID = rangeID.getCell(row, col).getValue();<br>  <br>        console.log(cellValue);<br>  <br>        function getNumberInsideBrackets(text: string) {<br>  <br>          // Run regex and get non-null result<br>          let result1 = text.match(regex);<br>          let result2 = regexFinal.exec(cellValue);<br>          let result3 = regexFront.exec(cellValue);<br>          let result = result1 ?? result2 ?? result3;<br>  <br>          // Check if ID value exists; if not, write non-null regex in the column<br>          if (result != null) {<br>            if (cellValueID != null) {<br>              rangeID.getCell(row, col).setValue(result[1]);<br>              console.log(`Result: &quot;${result[1]}&quot;`);<br>            }<br>          }<br>        }<br>  <br>        getNumberInsideBrackets(cellValue);<br>      }<br>    }<br>}</pre><p>Running this script will populate the ID column of the spreadsheet:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ksGx2vyHFnoCWwprLvxJ9g.gif" /><figcaption>Get ID script running</figcaption></figure><h4>Obtain columns names</h4><p>To obtain the columns names to compare it with the expected columns; in the first versions of the Flow I used a &quot;List Rows&quot; action, and with that treat the first line as a string and split it to get the columns names, but depending of what we have in the columns this splitting can be difficult (needing a huge expression with a lot of ifs and replaces) and lead to failure</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bG2PZ8d-i_Fu5dTHmHmprA.png" /><figcaption>Example of the first returned item from &quot;List Rows&quot; action</figcaption></figure><p>Once more, using an office script to get an array of the headers:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/917/1*auMBU6dy4AfmScwoqqs4QQ.png" /></figure><p>And compare it with the expected array of columns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PLHo-qGnlY47ifkkIS7kEg.png" /></figure><p>Now, this script has a particularity, we need an output from it (the array). To return data from script, you need to add the return type syntax to the main function:</p><pre>function main(workbook: ExcelScript.Workbook): string[] {<br><br>  function getColumnNames(): string[] {<br><br>    // Get active worksheet<br>    let currentWorksheet = workbook.getActiveWorksheet();<br><br>    // Get the used range of the worksheet<br>    let usedRange = currentWorksheet.getUsedRange();<br>    let lastColumnIndex = (currentWorksheet.getUsedRange().getLastColumn().getColumnIndex())<br>    //console.log(lastColumnIndex);<br><br>    // Get the column names<br>    let columnNames = usedRange.getResizedRange(1, usedRange.getColumnCount()).getValues();<br><br>    let columnList: string[] = [];<br><br>    // Log each column name<br>    for (let i = 0; i &lt; lastColumnIndex + 1; i++) {<br>      columnList.push(String(columnNames[0][i]));<br>    }<br><br>    return columnList;<br>  }<br><br>  // Execute the function and return<br>  return getColumnNames();<br>}</pre><p>With all that in hand you can check for wrong columns names, columns order or absence of columns and send feedback to user that uploaded the file to the SharePoint library, reducing any possibility of errors!</p><h3>Conclusion</h3><p>In this particular presented case, the office scripts were the tool that solved the problem, sometimes you will be tempted to use it to simple Tasks; but there&#39;s a tradeoff here that need to be weight (how much time you will spend writing and testing your code? There&#39;s a native action that does the same?)</p><p>Office Scripts offers even more for a wide range of challenges: you can pass parameters for the main script function (this is awesome) or even make external API calls, here&#39;s where your imagination goes and you really need to take a look at the documentation to expand your knowledge of this tool!</p><h3>References</h3><ul><li><a href="https://learn.microsoft.com/en-us/office/dev/scripts/">Office Scripts documentation - Office Scripts</a></li><li><a href="https://clavinfernandes.wordpress.com/2021/03/07/part-1-basics-of-excel-office-scripts/">Part 1 - Basics of Excel Office Scripts</a></li><li><a href="https://clavinfernandes.wordpress.com/2021/03/07/part-2-pass-data-from-script-to-power-automate/">Part 2 - Pass data from script to Power Automate</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f74efbffb02c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Microsoft Copilot Studio: Building a chatbot with generative IA fed with my portfolio, resume and…]]></title>
            <link>https://medium.com/@jean.dosher/microsoft-copilot-studio-building-a-chatbot-with-generative-ia-fed-with-my-portfolio-resume-and-3cf91577b7cc?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/3cf91577b7cc</guid>
            <category><![CDATA[resume]]></category>
            <category><![CDATA[copilot-studio]]></category>
            <category><![CDATA[generative-art]]></category>
            <category><![CDATA[power-platform-developers]]></category>
            <category><![CDATA[microsoft-copilot]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Sun, 03 Dec 2023 12:08:09 GMT</pubDate>
            <atom:updated>2023-12-03T12:08:09.424Z</atom:updated>
            <content:encoded><![CDATA[<h3>Microsoft Copilot Studio: Building a chatbot with generative IA fed with my portfolio, resume and personal sites</h3><p>Some of my experience trying to implement an AI-based bot to read my blogs, LinkedIn and some documents.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/999/0*ggkbdD_yRcu0Tmzk" /><figcaption>Copilot Announcing</figcaption></figure><h3><strong>Context: About Copilot Studio</strong></h3><p>Power Virtual Agent gets a Power UP! Now it is Copilot Studio with a lot of new and extended features.</p><blockquote>Microsoft Copilot Studio lets you create powerful AI-powered copilots for a range of requests — from providing simple answers to common questions to resolving issues requiring complex conversations. Engage with customers and employees in multiple languages across websites, mobile apps, Facebook, Microsoft Teams, or any channel supported by the Azure Bot Framework.</blockquote><h3>My case</h3><p>In almost all my recent interviews I needed to provide additional information about my resume and projects to the interviewers. Also, I needed to get a chance to implement an AI-based chatbot -perfect match!</p><p>I really enjoy staying updated in my resume and articles -so, I already have inputs, let&#39;s see some implementation details.</p><h3>Implementation</h3><p>For this implementation, I fed the generative AI with my blogs, personal site and LinkedIn:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OtpCsiHgGJaeECmTTNi0lg.png" /></figure><p>Also uploaded my exported LinkedIn profile a Text resume:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W-LlNIEMY5UyA7GdP7qixw.png" /><figcaption>Files Uploaded</figcaption></figure><p>For the LinkedIn site and the exported profile, I didn&#39;t get what I was expecting in the answers, so I decided to put it into a text file, which worked well after that. Furthermore, I tried too with some Notion&#39;s shared pages and Notion&#39;s exports -for this, result was not so good too.</p><h3>Result</h3><p>After a lot of testing (and some concessions), I embedded it in <a href="https://jpdosher-html-css-js.vercel.app/chat.html">my personal test page</a>.</p><p>I just changed the bot Icon, but the customization possibilities are huge, for this check the Copilot Documentation (<a href="https://learn.microsoft.com/en-us/microsoft-copilot-studio/customize-default-canvas?tabs=web">look and feel documentation</a>).</p><p>And, here it is:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CvPzNFG8iqt5TgDfas1uEw.png" /><figcaption>Embedded chatbot in personal page</figcaption></figure><h3>Final Considerations</h3><p>This implementation&#39;s main objective was to test the generative AI part of the Bot -The formerly Power Virtual Agents had a bunch of capacities, like http requests, events handling, adaptive cards integration, inputs handling (as a normal business bot case) and full look and fell customization.</p><p>I really struggled trying to force the context for all the sites, articles and files that I point to Copilot Studio to be the same (they all are practically the same thing!), after all I gave up and assume that this is where the &quot;Topics&quot; will have the job to &quot;cover the holes&quot; -For this, the new &quot;creating your own GPT&quot; in ChatGPT will do a way better job (and, thinking better, this is the same product, but poorly implemented in Copilot Studio).</p><p>Despite that, I had a good experience and I really understand that you need to put some hours into that to get it pretty (for your business).</p><p>Finally, the bot (after all it&#39;s a generative AI bot) can also use plugins to connect with AI Builder, Power Automate Flow, Power Platform connections and OpenAI plugins, imagine the power of that!</p><h3>References</h3><ul><li><a href="https://learn.microsoft.com/en-us/microsoft-copilot-studio/">Official Microsoft Copilot Studio documentation — Microsoft Copilot Studio | Microsoft Learn</a></li><li><a href="https://github.com/microsoft/BotFramework-WebChat">Bot Framework</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3cf91577b7cc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to filter Subgrid in a Model-driven App using code]]></title>
            <link>https://medium.com/@jean.dosher/how-to-filter-subgrid-in-a-model-driven-app-using-code-e9bca0e6562f?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/e9bca0e6562f</guid>
            <category><![CDATA[model-driven-app]]></category>
            <category><![CDATA[powerapps]]></category>
            <category><![CDATA[powerplatform]]></category>
            <category><![CDATA[javascript]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Sat, 11 Nov 2023 13:43:58 GMT</pubDate>
            <atom:updated>2024-10-09T10:02:17.602Z</atom:updated>
            <content:encoded><![CDATA[<p>Using Form Context and some code, it is possible to set the Subgrid filter.</p><p>This article assumes that you already have some familiarity with Model-Driven Apps and Form customization using code. But don&#39;t worry if you&#39;re not familiar with it, at the end of the article I suggest an Udemy course that will guide you through coding implementation.</p><h3>Context</h3><p>In the article “<a href="https://medium.com/@jean.dosher/solution-asset-management-built-with-model-driven-app-54d28b80301c">Solution: Asset Management (Built with Model Driven App) | by Jean Paul Dosher | Oct, 2023 | Medium</a>”, I pointed out a concern that I had with the screen “Assets”. When the user accesses an existent record the sub grid in botton of the Form don’t filter the columns Asset ID (ID do Ativo in Portuguese):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*USsUnkO5nFclGtkJG15nYQ.gif" /><figcaption>App without changes</figcaption></figure><h3>Client API</h3><p>The <a href="https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference">Client API</a> is a programming interface that empowers Power Platform developers to customize and extend model-driven apps, enabling data manipulation, event handling, implement business logic and integration with other UI components.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cOk8_Bs8WmVGO3JDd8zEIQ.png" /><figcaption>Client API Objects</figcaption></figure><p>In our case, basically we want to read if there is an Asset ID filled in the field &quot;Id do Ativo&quot;, if it has value, turn on the Filter (using that value).</p><p>To read columns contain data in the model-driven apps form or grids. Use the formContext.data.entity.attributes collection or the formContext.getAttribute shortcut method to access a collection of columns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8SLiPR88iEu1ppv5hjsNpg.png" /><figcaption>formContext diagram</figcaption></figure><p>formContext.getAttribute(arg).getValue()</p><p>The argue (arg) will be the logical name of the Table column, you can get that in the proprieties pane in the Form:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gZd0CXvrBRnAP4QHxsO2NA.png" /><figcaption>logical name of the Asset ID column</figcaption></figure><p>Also (one easy way), you can play a bit with the code and obtain all attributes leaving the arg empty: console.log(formContext.getAttribute());</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kpfDmh_UbNJzTkmvaLzgxw.png" /></figure><p>With this, now we can write the full expression with the method getValue():</p><p>dataformCOntext.getAttribute(&quot;jpd_name&quot;).getValue()</p><p>This will be use only to &quot;not null&quot; validation. For the later action of filtering, we&#39;&#39;ll need to use the GUID of the record -this will be passed to the filter to get the unique value of the record.</p><p>To get the GUID of the record the data propriety and his entity hierarchy (see formContext diagram picture) along with the &quot;getId&quot; method:</p><p>formContext.data.entity.getId();</p><p>With this expression, the response will be something like this: &quot;{EEB65B08-CE5A-EE11–8DF0–6045BD3B2479}&quot;</p><h4><a href="https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/grids">Subgrid Context</a></h4><p>&quot;Grids present data in a tabular format in model-driven apps. Grids can span the entire form or can be one of the items on a form; the latter are called <em>subgrids</em>.&quot;</p><p>For Subgrid/Grid handling, firstly you will need to obtain the Control element that stores the Subgrid:</p><blockquote>A control represents an HTML element present on the form. Some controls are bound to a specific column, whereas others may represent unbound controls such as an IFRAME, web resource, or a subgrid that has been added to the form.</blockquote><blockquote>The control object provides methods to change the presentation or behavior of a control and identify the corresponding column. You access controls using one of the following collections:</blockquote><blockquote>formContext.ui.controls</blockquote><blockquote>formContext.ui Section.controls</blockquote><blockquote>formContext.data.entity Attribute.controls</blockquote><blockquote>The formContext.<a href="https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/controls/getcontrol">getControl</a> method is a shortcut method to access formContext.ui.controls.get.</blockquote><p>For my example: formContext.getControl(&quot;Subgrid_new_1&quot;)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/854/1*7hVcU3lJ6TDVyhJ2daLTsg.png" /><figcaption>Grid Context Hierarchy</figcaption></figure><p>Moving on; in the Grid Context, the methods used to get and set the Filter (getFetchXml and setFilterXml)are not documented in the oficial available docs yet, but you can see it <a href="https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference/grids/gridcontrol/getfetchxml?source=docs">here</a>.</p><p>With getFetchXml you can see the structure of the grid to use as base for the Filter:</p><pre>&lt;fetch version=&#39;1.0&#39; mapping=&#39;logical&#39; <br>savedqueryid=&#39;79f7e750-6a6b-4b11-b812-67cc6cac844c&#39; <br>eturntotalrecordcount=&#39;true&#39; page=&#39;1&#39; count=&#39;4&#39; no-lock=&#39;false&#39;&gt; <br>&lt;entity name=&#39;jpd_alocacaodeativo&#39;&gt;<br>&lt;attribute name=&#39;jpd_alocacaodeativoid&#39;/&gt;<br>&lt;attribute name=&#39;jpd_name&#39;/&gt;<br>&lt;order attribute=&#39;jpd_name&#39; descending=&#39;false&#39;/&gt;<br>&lt;filter type=&#39;and&#39;&gt;<br>condition attribute=&#39;statecode&#39; operator=&#39;eq&#39; value=&#39;0&#39;/&gt;<br>&lt;/filter&gt;<br>&lt;attribute name=&#39;jpd_colaborador&#39;/&gt;<br>&lt;attribute name=&#39;jpd_datadealocacao&#39;/&gt;<br>&lt;attribute name=&#39;jpd_retornarate&#39;/&gt;<br>&lt;attribute name=&#39;jpd_iddoativo&#39;/&gt;<br>&lt;link-entity alias=&#39;a_e5d2f7baeafd4024b47652d6c13sk11&#39; name=&#39;jpd_ativo&#39; <br>to=&#39;jpd_iddoativo&#39; from=&#39;jpd_ativoid&#39; link-type=&#39;outer&#39; visible=&#39;false&#39;&gt;<br>&lt;attribute name=&#39;jpd_nomedoativo&#39;/&gt;<br>&lt;/link-entity&gt;<br>&lt;/entity&gt;<br>&lt;/fetch&gt;</pre><p>In the example I add to the &lt;filter&gt; the condition attribute: &lt;condition attribute=&#39;jpd_iddoativo&#39;operator=&#39;eq&#39;value=&#39;{currentAssetId}&#39;/&gt;, set the filter with ˜setFilterXml method:gridContext.setFilterXml(fetchXml);</p><p>And, finally, refresh the grid to show modifications applied with formContext.getControl(&#39;Subgrid_new_1&#39;).refresh()</p><h3>The code</h3><p>I didn&#39;t explain all the steps of code, like the variable&#39;s attributions, the if condition, etc. But here it is, the sketch of the code used.</p><pre>this.formOnLoad = function(executionContext) <br>{<br>    <br>   //obtain the main object  <br>   var formContext = executionContext.getFormContext();<br>    <br><br>    //get attribute to see if it is null<br>    jpdName = formContext.getAttribute(&quot;jpd_name&quot;).getValue();<br>    console.log(jpdName);<br><br>    if(jpdName !== null){<br><br>    //get current asset id<br>    currentAssetId = formContext.data.entity.getId();<br>       <br>    //grid context and fetch xml<br>    gridContext = formContext.getControl(&quot;Subgrid_new_1&quot;);<br>    exampleFetch = gridContext.getFetchXml();<br><br>    <br>    //build xml with filtered Asset Id<br>    var fetchXml = &quot;&lt;fetch version=&#39;1.0&#39; mapping=&#39;logical&#39; <br>savedqueryid=&#39;79f7e750-6a6b-4b11-b812-67cc6cac844c&#39; r<br>eturntotalrecordcount=&#39;true&#39; page=&#39;1&#39; count=&#39;4&#39; no-lock=&#39;false&#39;&gt;&quot; +<br>    &quot;&lt;entity name=&#39;jpd_alocacaodeativo&#39;&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_alocacaodeativoid&#39;/&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_name&#39;/&gt;&quot;+<br>    &quot;&lt;order attribute=&#39;jpd_name&#39; descending=&#39;false&#39;/&gt;&quot;+<br>    &quot;&lt;filter type=&#39;and&#39;&gt;&quot;+<br>    &quot;condition attribute=&#39;statecode&#39; operator=&#39;eq&#39; value=&#39;0&#39;/&gt;&quot;+<br>    &quot;&lt;condition attribute=&#39;jpd_iddoativo&#39; operator=&#39;eq&#39; value=&quot;+&quot;&#39;&quot;+<br>    currentAssetId+&quot;&#39;&quot;+<br>    &quot;/&gt;&quot;+<br>    &quot;&lt;/filter&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_colaborador&#39;/&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_datadealocacao&#39;/&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_retornarate&#39;/&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_iddoativo&#39;/&gt;&quot;+<br>    &quot;&lt;link-entity alias=&#39;a_e5d2f7baeafd4024b33652d6c13sk11&#39; <br>    name=&#39;jpd_ativo&#39; to=&#39;jpd_iddoativo&#39; from=&#39;jpd_ativoid&#39; <br>    link-type=&#39;outer&#39; visible=&#39;false&#39;&gt;&quot;+<br>    &quot;&lt;attribute name=&#39;jpd_nomedoativo&#39;/&gt;&quot;+<br>    &quot;&lt;/link-entity&gt;&quot;+<br>    &quot;&lt;/entity&gt;&quot;+<br>    &quot;&lt;/fetch&gt;&quot;;<br>    console.log(fetchXml);<br><br>    gridContext.setFilterXml(fetchXml);    <br><br>    //refresh grid to show filtered data<br>     formContext.getControl(&#39;Subgrid_new_1&#39;).refresh();<br>    }<br><br>}<br><br></pre><h3>Conclusion</h3><p>Model-driven App is handy if well implemented and designed as a solution, not a tool.</p><p>Sometimes some business logic or customizations will only be possible with code (that&#39;s my case for this App), I strongly recommend you to take a deep look at the documentation linked bellow -it&#39;s not so big, and you can focus firstly looking at the main objectives/features.</p><p>For the code, I recommend putting some error handling in it. You will notice that in my code there&#39;s some &quot;console.log&quot;, that&#39;s for debugging and understanding propose (you can you the Dev-tools of your browser to see it)</p><h4>The result:</h4><p>Now, with the implementation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AC-tx4Qm1C-5krB-sFYkdA.gif" /><figcaption>App after implementation</figcaption></figure><h3>References</h3><ul><li><a href="https://learn.microsoft.com/en-us/power-apps/developer/model-driven-apps/clientapi/reference">Client API Reference for model-driven apps — Power Apps | Microsoft Learn</a></li><li><a href="https://www.udemy.com/course/pl-400-microsoft-power-platform-developer-course/">Phillip Burton Udemy PL-400 course</a></li><li>Gi</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e9bca0e6562f" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Solution: Asset Management (Built with Model Driven App)]]></title>
            <link>https://medium.com/@jean.dosher/solution-asset-management-built-with-model-driven-app-54d28b80301c?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/54d28b80301c</guid>
            <category><![CDATA[power-platform-developers]]></category>
            <category><![CDATA[model-driven-app]]></category>
            <category><![CDATA[dataverse]]></category>
            <category><![CDATA[powerapps]]></category>
            <category><![CDATA[application-development]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Sat, 21 Oct 2023 10:33:30 GMT</pubDate>
            <atom:updated>2023-10-21T19:03:00.601Z</atom:updated>
            <content:encoded><![CDATA[<p>Complete solution with Approvals and Microsoft Flow to support the business processes.</p><h3>Context</h3><p>This App decentralizes the control of asset allocations and uses the contacts/employes database already implemented in the organization’s Dataverse.</p><p>With this we get rid of yet another use case of Excel spreadsheets for control and database. Also guaranteeing the security of information (maintained in the organization’s tenant) and the continuous business process.</p><p><strong>What you can do with the app:</strong></p><ul><li>Create, update, deactivate and delete asset and asset allocations</li><li>Assign an asset to an employee for a period</li><li>Check approval and allocation verification status</li><li>View collaborator and asset details</li><li>View Power BI reports regarding the allocation process</li></ul><blockquote><strong>When should I use Model-Driven Apps?</strong></blockquote><blockquote>With Model-Driven you don’t need to explicitly worry about the data model — logically it is possible, and advisable, but here one of the benefits is starting from the business rule.</blockquote><h3>Application Building Steps:</h3><ol><li>Construction of tables in Dataverse<br>1.1 Asset<br>1.2. Asset Allocation<br>1.3. Contact<br>1.4 Asset Checks</li><li>FORMS construction (Main Forms, Quick View Forms, Quick Create Forms)</li><li>Build Views</li><li>Implement Business rules</li><li>Sub-grids and Lookup views</li><li>Business Process Business Flows</li><li>Approval Automation</li><li>Finally, creating the Application</li><li>Embedded Dashboards (in this case, I just insert an aleatory Powe BI report)</li><li>Additional automation — change item status with Power Automate</li><li>Sharing and Security</li><li>Testing and debugging</li></ol><h3>Final result</h3><p>As the result, now users can search for Assets, see its features, if they are located and open a process to allocation (1st phase). For this phase, if the Allocation is checked as “Out of Policy”, an Approval process will be opened by a Power Automate Flow.</p><p>If the Asset is available, the mandatory fields are filled and the Approval process was succeeded (if is out of policy), the Allocation Process will go to the second phase, “Asset Verification”, which consists of the inspection of the Asset. (2nd phase)</p><p>For the third phase and the forty phase the steps are simpler, they consist in check the Asset as “allocated”, define a return date and return the Asset -for these two final phases another Power Automate Flow was used for updating Asset Allocation Status</p><p>Here it is the screenshots of the APP:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2y47OMKWJ5FTItA2y4q6yA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C1Rr09xNvxOYd81NgJvt0A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P_cfCkMLEUTWyxR67lIAsA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*BApkb5JPEInkeGFWv1BnUw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N_k1eOSAP0Dsa86tDfcEfw.png" /><figcaption>APP and Flows Screenshots</figcaption></figure><h3>Future features and improvements</h3><p>In the Asset item screen, there is a view with the allocation&#39;s history. I would like to have this lower view filtered by the field — just like in the Contact Form (quick view)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pFGT9q2eIb0rBUuqVLy7dw.png" /></figure><p>This will require to filter the view in using JavaScript, you can use the <strong>Xrm.Page.getControl </strong>method to get the view control and then use the <strong>addCustomFilter </strong>method to add a custom filter to the view. But for that, I’ll shown in a future article.</p><h4>Links and References:</h4><p><a href="https://jpdosher.notion.site/Assets-management-7ad5ae393f664f1bbdf19b016ee47575?pvs=4">My Notion page with project notes</a><br><a href="https://github.com/jpdosher/Power-Platform_model-driven-apps">Git Repository for the entire solution</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54d28b80301c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Use Graph Api in Power Automate to read (very)large Excel files from SharePoint]]></title>
            <link>https://medium.com/@jean.dosher/use-graph-api-in-power-automate-to-read-very-large-excel-files-from-sharepoint-54f87cfcf659?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/54f87cfcf659</guid>
            <category><![CDATA[microsoft-graph]]></category>
            <category><![CDATA[power-automate]]></category>
            <category><![CDATA[rest-api]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[power-platform-developers]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Wed, 18 Oct 2023 21:20:38 GMT</pubDate>
            <atom:updated>2023-12-07T14:09:28.308Z</atom:updated>
            <content:encoded><![CDATA[<p>How I reduced execution time from <strong>one</strong> <strong>hour to nine seconds </strong>for an Excel obtention scope in Power Automate.</p><h3>Current scenario</h3><h4>Quantity of items:</h4><p>The sample file has 50K items:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/469/1*0he94ZiOG87ApdQlCwJUfA.png" /></figure><h4>Running time:</h4><p>The scope of obtaining data from an Excel File hosted in a SharePoint Library takes around an hour to execute (in my study environment took eight hours 😭):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/458/1*cSKTkpWL_uujjdavrLl4UA.png" /><figcaption>For each action</figcaption></figure><p>In the Production environment this quantity usually takes about an hour to run.</p><p>Occasionally, the “do until” action fails in the last iteration…. 😖</p><h3>Solution: Use Graph API!</h3><p>Microsoft Graph API is a single API that connects to data and insights from Microsoft cloud services, such as Microsoft 365, Windows, and Enterprise Mobility + Security. It’s powerful!</p><p>To use the HTTP with Azure AD connector or the Invoke HTTP request (easier way) action in Power Automate, you need to have a Power Automate per user or per flow license. These licenses are not included in the Microsoft 365 license, and they have different pricing and features. You can find more information about the Power Automate licensing options in <a href="https://powerautomate.microsoft.com/en-us/pricing/">this link</a>.</p><h4>Drive API</h4><p>You can use Microsoft Graph to read and modify Excel workbooks stored on OneDrive for Business, your SharePoint site, or your Group Drive. The Workbook resource (or Excel file) contains all other Excel resources through relationships. You can access a workbook through the Drive API by identifying the file’s location in the URL.</p><p>For example:</p><pre><a href="https://graph.microsoft.com/v1.0/me/drive/items/%7Bid%7D/workbook/">https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/</a><br>https://graph.microsoft.com/v1.0/me/drive/root:/{item-path}:/workbook/&gt;</pre><p>You can access a set of Excel objects (such as Table, Range in Table, or Chart) using the standard REST APIs to perform create, read, update, and delete (CRUD) operations in the workbook.</p><p>For example, the request will be:</p><pre>GET- https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets</pre><p>Will return a collection of worksheet objects that are part of the workbook.</p><p>The Excel REST API only supports workbooks formatted in an Office Open XML file. Workbooks with .xls extension are not supported.</p><h3>Application</h3><p>By applying this request, pagination is not necessary (the documentation does not mention restrictions), one of the reasons that the acquisition time is surprisingly shorter:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*ZapweFF3X5EmzGQPlmtpvA.png" /><figcaption>Execution of the same file in a study environment</figcaption></figure><h4><strong>Requisitions</strong></h4><p>The following request will be used to obtain Excel rows:</p><pre>https://graph.microsoft.com&gt;;v1.0/sites/{Site_ID}/drive/root:/{Item-path}:/workbook/worksheets/{Id|name_Worksheet}/tables/{Id/name_Table}/rows</pre><p>Let&#39;s see each parameter/placeholder:</p><h4><strong>Parameters/Placeholders:</strong></h4><p><strong>Site_ID:</strong> obtained with a request to the Graph “sites” endpoint:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/550/1*cFbGOPG5dJTGhV9DA7Cl-A.png" /></figure><p>In the example:</p><pre>https://graph.microsoft.com/v1.0/sites/xl86x.sharepoint.com:/sites/CursoSharepointBsico</pre><p>Example of the value obtained to be used in the main request {Site_ID}:</p><pre>&quot;id&quot;: “xl86x.sharepoint.com,fab2ddf4-0490-4ce7-9a01-5494b0ebc15e,3c519f39-1169-4d2d-b470-26bfdcfdc2d9”</pre><blockquote>This may be a point for improvement, the documentation does not specify what the value “3c519f39–1169–4d2d-b470–26bfdcfdc2d9 is</blockquote><p><strong>Item-path:</strong> Is obtained with an action “get item from Trigger” or directly from the trigger output (triggeroutputs):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/570/1*TS99iDxTzIP6UyLbPOxAhw.png" /></figure><p><strong>Id|name_Worksheet: </strong>Worksheet from the file</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MERosJsb1N4sFDfyorc7LQ.png" /></figure><p>This can be obtained as a native Excel action (as is already standard in Load Flows). For the example I just “fixed” the value</p><p><strong>Id/name_Table: </strong>Also, can be achieved with a native Excel action, but again, for the example, I entered the value manually. It can be obtained in the Excel file as shown below (remembering that the data must be formatted as a table):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c4EPAkfpS3bCognvbb0UQg.png" /><figcaption>Path to obtain the name of the data table</figcaption></figure><h4>Finally, the complete request:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/554/1*Lk50IHjSSKL0y-U8KMdrCg.png" /></figure><p>URI from the example:</p><pre>https://graph.microsoft.com/v1.0/sites/xl86x.sharepoint.com,fab2ddf4-0490-4ce7-9a01-5494b0ebc15e,3c519f39-1169-4d2d-b470-26bfdcfdc2d9/lists/9e4e3a0e-1a1b-44b6-a11a-85e542a7d2cc/drive/root:/SampleXLSFile_table.xlsx:/workbook/worksheets/Sample/tables/table/rows</pre><p>You can manipulate the request using “OData query parameters”, such as $top, $skip etc:</p><pre>https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/tables/{id|name}/rows?$top=5&amp;$skip=5</pre><h4>Request output</h4><p>This is an example of a request return (in case of OK response: 200):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/576/1*6rpo3oon8Ry5EBdDh9gOFg.png" /></figure><p>And this is from the Sample File:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5RJi_srEckHwsQzgLUmMag.png" /><figcaption>Last item of the request action output</figcaption></figure><h4>Graph Explorer</h4><p>Just a tip: a practical tool for quick testing and learning is <a href="https://developer.microsoft.com/en-us/graph/graph-explorer)">Graph Explorer</a>, in which you have direct links to the endpoint documentation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HFkVAHSIz2GgxRZ1sFDEgQ.png" /></figure><h3>Improvements for the Flow</h3><p>The objective of this article is just to demonstrate the effectiveness and efficiency gains using Graph, for a dynamic and error proof Flow I suggest use “Dynamic Contents”, such as:</p><ul><li>Auto Format file and Get Sheet name to Table before Graph Api request using native Excel actions (for those action, native action will perform well)</li></ul><h3>References</h3><p><a href="https://learn.microsoft.com/en-us/graph/auth-v2-service?tabs=http">Get access without a user — Microsoft Graph</a></p><p><a href="https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0">Working with Excel in Microsoft Graph — Microsoft Graph v1.0</a></p><p><a href="https://developer.microsoft.com/en-us/graph/graph-explorer">Graph Explorer | Try Microsoft Graph APIs — Microsoft Graph</a></p><p><a href="https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting">https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=54f87cfcf659" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Como eu estudo!? -e talvez, como você pode estudar (e como isso não pode ser útil)]]></title>
            <link>https://medium.com/@jean.dosher/como-eu-estudo-e-talvez-como-voc%C3%AA-pode-estudar-e-como-isso-n%C3%A3o-pode-ser-%C3%BAtil-96eeed873d13?source=rss-cfc32b616f91------2</link>
            <guid isPermaLink="false">https://medium.com/p/96eeed873d13</guid>
            <category><![CDATA[study]]></category>
            <category><![CDATA[notion]]></category>
            <dc:creator><![CDATA[Jean Paul Dosher]]></dc:creator>
            <pubDate>Fri, 18 Aug 2023 15:16:31 GMT</pubDate>
            <atom:updated>2023-09-30T10:25:35.704Z</atom:updated>
            <content:encoded><![CDATA[<h3>Como eu estudo!? E, talvez, como você pode estudar.</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*50Ob2_le7XLDcsh60Ld7Qg.jpeg" /></figure><h3>Antes de tudo:</h3><p>Gente, não existe fórmula mágica. De coração, o único conselho que faço questão de dar é que “cada um tem seu jeito”, não perca tempo procurando por conteúdos sobre “como estudar” (que coisa, não?!) -<strong>sério</strong>, por experiência; a coisa acaba virando uma <em>procrastinação “gourmet”</em>.</p><h3>Mas caso você precise reafirmar alguns conceitos:</h3><p>Bom, agora que desabafei vou deixar alguns lembretes que deixei em uma folha impressa à frente da minha mesa:</p><ul><li>Metacognição- Quais são os pontos fracos e fortes do meu método do meu aprendizado?</li><li>Planejar seus estudos, monitorar se você está aprendendo ou não e sempre avaliar seu desempenho. (Ex: O que não funcionou tão bem no método? O que foi mais confuso no material que estudei? Eu tenho algum conhecimento prévio que me ajude neste curso? Concepções que eu tinha e mudaram depois deste curso…</li><li>Crie Acrônimos- Memorizar fórmulas ou sequência de palavras (Ex: DS, DSA)</li><li>Faça conexões, crie mapas mentais 🗺️</li><li>Elabore Sínteses (escreva 3 principais pontos que você aprendeu)</li><li>Crie diagramas de interrogação elaborativa (Por quê? Como? Por que isso é verdade?)</li></ul><h4>Mais sobre conselhos e recomendações:</h4><ul><li>Auto explicação- Explique e pense Em voz alta</li><li>Não espere entender tudo para ir para o próximo passo/matéria</li><li>Leia com curiosidade</li><li><strong><em>Não estude apenas teoria!!!</em></strong></li><li>Método ART (Aprenda, Revise, Teste)</li><li>Explicabilidade — você sabe explicar o que está acontecendo? sabe explicar para outros públicos? (DataViz)</li><li>O foco não é a ferramenta, é a resolução de problemas</li><li>Não tome notas igual a um doido, isso aumenta a carga cognitiva</li><li>Explore o conhecimento (desenvolva autonomia)</li><li>Construa 🏗️</li></ul><p>💡 <strong>Troque “preciso estudar” por “preciso passar no vestibular, preciso desenvolver um game, preciso fazer esse bootcamp para conseguir uma recolocação” → nessa hora, a ideia é “enganar” o mecanismo de recompensa do cérebro!</strong></p><h3>Referências</h3><ul><li><a href="https://focus.meisterlabs.com/como-tomar-notas-eficazes-usando-mapas-mentais/?lang=pt-br">Como tomar notas eficazes usando mapas mentais — Focus</a></li><li><a href="https://www.youtube.com/watch?v=g7j_CoKD1Xs:">https://www.youtube.com/watch?v=g7j_CoKD1Xs</a> -How to take notes</li><li><a href="https://www.mindmeister.com/mind-map-examples:">https://www.mindmeister.com/mind-map-examples</a> -exemplos de mapas mentais</li><li><a href="https://www.mindmeister.com/pages/training:">https://www.mindmeister.com/pages/training</a> -Treinamentos do mindmeister</li><li><a href="https://chatmind.tech/:">https://chatmind.tech/</a> -mudou minha vida, ferramenta que gera mapas automáticos usando AI</li></ul><p><em>Este artigo é uma adaptação do meu Notion, caso queira acessar, deixei compartilhado </em><a href="https://jpdosher.notion.site/Como-estudar-863e953d9e7149a68ec812adeb7afeb6?pvs=4"><em>neste link</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=96eeed873d13" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>