cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenDonahue
Skilled Sharer
Skilled Sharer

How to Update a Row in Excel using Dynamic Column Names

The Setup

I am trying to dynamically reference which column to insert a datum. Within 3 sections, I have several columns. They are unimaginatively entitled, S1Q1, S1Q2, S1Q3, ...., S2Q1, S2Q2, S2Q3, ...., S3Q1, S3Q2, S3Q3, .... 

 

I have nested loops to handle the referencing, so Do Until intSectNum = 3 and within that a do until intResNum = 40 (40 questions/pieces of data).

 

After some, let's just call it "stuff," I arrive at what should be put into the column S1Q1. I can create 120 conditionals with 120 Update a Row blocks, but that seems, ... inelegant. Looking at the Peek Code for an Update a Row, I see this:

 

 

 

{
    "inputs": {
        "host": {
            "connectionName": "shared_excelonlinebusiness_1",
            "operationId": "PatchItem",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"
        },
        "parameters": {
            "source": "sites/healthcareswfl.sharepoint.com,6ab323b7-b8b5-4bdd-be9b-7ee3ab8a50a2,07f4bc58-fbde-4abd-9b4f-02cdc3f5c8dd",
            "drive": "b!tyOzarW43Uu-m37jq4pQoli89Afe-71Km08CzcP1yN2Re8n54SphSrkCQC9GpCzV",
            "file": "01V7BH3KXAPZ637AX7FVBJOI74CFKTWDL6",
            "table": "{C8924A87-2B75-4312-BC66-5F3AA1C40B63}",
            "idColumn": "S1Q1",
            "id": "@variables('strResValue')"
            "item/S1Q1": "Yes",
        },
        "authentication": "@parameters('$authentication')"
    },
    "metadata": {
        "01V7BH3KXAPZ637AX7FVBJOI74CFKTWDL6": "/AppDevFolder-CCA/CCADataCollection.xlsx"
    }
}

 

 

 

 

The Solution

Ideally, I'd like to do something like this where it says "'item/S1Q1': ":

 

 

 

concat("""","item/S",variables('intSectNum'),"Q",variables('intQNum'),"""",": ","""",variables('strRespValue'),"""",",")

 

 

 

The research I have done so far suggests that Power Automate does not have this functionality, but that post was from 14 months ago, so I am hopeful.

 

The Question

Is it possible to dynamically reference a column name to be updated?

Alternatively, do you have a solution that is better than creating those 120 conditionals and the 120 update a row blocks?

 

The Thanks

Thank you for your assistance in this.

Being able to do this would add a very powerful tool to my bag of tricks, and will better my code skills.

2 ACCEPTED SOLUTIONS

Accepted Solutions
BenDonahue
Skilled Sharer
Skilled Sharer

I am still not able to find the solution I want, however, I found, and implemented, a solution that is far better than creating seperate blocks for wach question in each section, but took longer to implement than the solution I had hoped for: 

 

concat("""","item/S",variables('intSectNum'),"Q",variables('intQNum'),"""",": ","""",variables('strRespValue'),"""",",")

 

 It involves conditionally adding the piece of data to the cell, based on where in the two loops we are. Here is the code for section 1, question 1:

 

if(and(equals(variables('intSectNum'), int(1)), equals(variables('intQNum'), int(1))), variables('strResValue'), null)

 

Using Excel, I created a conditional for each question and put them in the Update a Row block, like so:

columnInterpolationWorkaround1of6.pngcolumnInterpolationWorkaround2of6.pngcolumnInterpolationWorkaround3of6.pngcolumnInterpolationWorkaround4of6.pngcolumnInterpolationWorkaround5of6.pngcolumnInterpolationWorkaround6of6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As this is not the solution I had hoped for, I will not mark it as a solution for another few days, at least. At that point, I will assume that this is the best practices for this platform.

View solution in original post

GeoffRen
Microsoft
Microsoft

Have you considered using Office Scripts (specifically Office Scripts with Power Automate)? This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So, in your case, you can have a script that accepts the column name and value to update as parameters. Then, in the script, you would explicitly get the column to update by the column name parameter and then update it with the value parameter any way you want (since it's just code you can do this using whatever conditions, formatting, etc that you want).

 

I can help you construct a script for this if you want help. Though I don't think I completely understand what your Excel table looks like or how you want to insert the value so a picture or two would be helpful here!

 

View solution in original post

6 REPLIES 6
BenDonahue
Skilled Sharer
Skilled Sharer

I am still not able to find the solution I want, however, I found, and implemented, a solution that is far better than creating seperate blocks for wach question in each section, but took longer to implement than the solution I had hoped for: 

 

concat("""","item/S",variables('intSectNum'),"Q",variables('intQNum'),"""",": ","""",variables('strRespValue'),"""",",")

 

 It involves conditionally adding the piece of data to the cell, based on where in the two loops we are. Here is the code for section 1, question 1:

 

if(and(equals(variables('intSectNum'), int(1)), equals(variables('intQNum'), int(1))), variables('strResValue'), null)

 

Using Excel, I created a conditional for each question and put them in the Update a Row block, like so:

columnInterpolationWorkaround1of6.pngcolumnInterpolationWorkaround2of6.pngcolumnInterpolationWorkaround3of6.pngcolumnInterpolationWorkaround4of6.pngcolumnInterpolationWorkaround5of6.pngcolumnInterpolationWorkaround6of6.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As this is not the solution I had hoped for, I will not mark it as a solution for another few days, at least. At that point, I will assume that this is the best practices for this platform.

View solution in original post

asd2525
Helper I
Helper I

That is a very interesting solution but it got the job done. You might want to look into Power Query. It is a great way to combine data from multiple sources.

Another possible option would be to look at Microsoft Forms. It will automatically collect data from the form as people submit it. Depending on how you set up the form you can have it so the excel will be a living document or one that you have to generate each time. I obviously suggest the living document method as the generating one has a maximum number of rows it can pull. One down side to Microsoft Forms is you have a max of 100 questions and looking at your pictures that might not be enough. 

Anyway I hope one of those options might help when you come across similar situations in the future. 

I will absolutely look into Power Query. I have heard of it, but haven't yet had a chance (made the time) to look at it.

I front loaded the effort to abstract this thing for possible application in future survey/audit assignments, so I used the max number of questions that Forms allows which, on my end, is 200 (200 over a total of 5 potential sections). That includes section headers, etc.. I structured in 5 sections, 40 questions each, with some removed from section 5 for the titles, headers and such.

And I will take you designation of "interesting" as a compliment so, thank you :)))  I'd like to take "interesting" and turn it into "elegant."

Thanks for the heads up and for the interest in this cul-de-sac I find myself in. 

GeoffRen
Microsoft
Microsoft

Have you considered using Office Scripts (specifically Office Scripts with Power Automate)? This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So, in your case, you can have a script that accepts the column name and value to update as parameters. Then, in the script, you would explicitly get the column to update by the column name parameter and then update it with the value parameter any way you want (since it's just code you can do this using whatever conditions, formatting, etc that you want).

 

I can help you construct a script for this if you want help. Though I don't think I completely understand what your Excel table looks like or how you want to insert the value so a picture or two would be helpful here!

 

View solution in original post

I am not the Original Poster, but can I take you up on that? I have a similar problem. I would like to determine the current date, find the column whose header is that date, and then populate the rows in that column. If I use OP's workaround, I will have to rewrite the whole thing every year. But I have never written a script for Excel and do not know where to start.

Sorry, @GeoffRen , for ghosting like that. The solution, in this case, was to switch to Power Apps and Dataverse, which bypassed the issue I powted on.

I, too, would very much like to see what Power Automate-Excel javascript code looks like.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,899)