Add a drop-down menu in an Excel file column.

[muhepd1]

Hello,

I have an Excel file that is being created through another type of automation (Business Objects) and distributed in an email.

I also have a Power Automate flow that is taking that file from the email and formatting a table with its values.

One of the columns is called Decisions. Today, after the file is distributed, we are manually typing the Decisions in each cell of the column.

Finally, we would re-distribute the file manually in another email.

The problem this is causing is that Decisions are not standardized, meaning, the same decision could be written different, example: Proceed, Procced, proceed, proced, in short, there could be different ways the same decision could be written.

What we would like is for the same Power Automate flow that is formatting the table, to then format the Decisions column and add a drop-down menu on each of its cells with pre-defined decisions. This way, no one will have to write the decision, just select it.

Is this feasible? Could you help me build the flow?

Thanks in advance. 

[David_MA]

Open your Excel spreadsheet and record a script to add a new sheet to your workbook with the valid choices for your decisions field and use data validation to apply these choices to the Decisions column of the workbook. You can then use the Run Office Script action to run the script to do this as part of your flow. You can learn more about this at Run Office Scripts with Power Automate - Office Scripts | Microsoft Learn

[muhepd1]

Hi, thank you for replying.

That is a good idea, however, we get a new excel file everyday with different amount of rows, if I select the entire column, would my table automatically increase the amount of rows to infinite when I apply this script?

Thanks.

[David_MA]

The number of rows shouldn't matter as long as your Decisions value is always in the same column. When you record the script, just be sure to select the entire column when recording the script to set up the data validation. 

[muhepd1]

Ok, I did but Excel is throwing an error on the script recording when I apply the data validation, the data validation is applied to the Excel sheet, but the action is not recorded in the script:

Any idea of what it could be?

function main(workbook: ExcelScript.Workbook) {
	// Add a new worksheet
	let sheet1 = workbook.addWorksheet();
	// Set range A1:A9 on sheet1
	sheet1.getRange("A1:A9").setValues([["Proceed"],["Awareness"],["Re-Scope"],["Re-Plan"],["Re-Scope/Re-Plan"],["Follow Up"],["Conflict"],["Risk"],["Awareness/Coordination"]]);
	// An error occurred while recording this event.
	// Set sheet visibility to hidden
	sheet1.setVisibility(ExcelScript.SheetVisibility.hidden);
}

[David_MA]

I tried to create the script and I get the same error. Everything worked up until doing the data validation step. I've only created a few Office Scripts, and none included data validation. I asked ChatGPT and it says this can be tricky and offered the following to create the script. I don't have time right now to try this, but maybe you can play with it to get it to work.

function main(workbook: ExcelScript.Workbook) {
  // Define the source worksheet and range where your list of values is located
  let sourceSheet = workbook.getWorksheet("Sheet2"); // Replace "Sheet2" with your actual sheet name
  let sourceRange = sourceSheet.getRange("A1:A10"); // Adjust the range as per your requirement

  // Fetch the values from the source range
  let listOfValues = sourceRange.getValues();

  // Define the target worksheet and range where you want to apply data validation
  let targetSheet = workbook.getActiveWorksheet();
  let dataRange = targetSheet.getRange("A1:A10"); // Adjust the range as per your requirement

  // Set data validation on the specified range
  let validationRule = {
    list: {
      inCellDropDown: true,
      source: listOfValues,
    },
  };

  dataRange.setDataValidation(validationRule);

  // Hide "Sheet2" at the end
  sourceSheet.setVisibility(ExcelScript.SheetVisibility.hidden);
}

The above doesn't include creating Sheet2 or adding the values, but since that worked, I would record the script up to that part and then try adding the above. You'll need to adjust A1:A10 to match the rows in Sheet2 where your choices are located.

[muhepd1 · Accepted solution]

Hi David,

I tried your code above but it was giving me errors as the setDataValidation function was not recognized. However, you pointed me in the right direction. After trying many different options with chatgpt myself, I came across a code very closed to my solution in a stackoverflow post. excel - Office Scripts Apply Data Validation to Table Column - Stack Overflow

By reading other official Excel material in terms of scripts and data validation with a list, and using part of the solution in that link above, I was able to create my code:

function main(workbook: ExcelScript.Workbook) {
	let tbl = workbook.getTable("Table1")
	let targetColumn = tbl.getColumn("TAB Resolution")
	let dataValidation = targetColumn.getRangeBetweenHeaderAndTotal().getDataValidation();

	// Set the content of the drop-down list.
	dataValidation.setRule({
		list: {
			inCellDropDown: true,
			source: "Proceed, Awareness, Coordination, Re-Scope, Re-Plan, Re-Scope/Re-Plan, Follow Up, Conflict, Risk, Awareness/Coordination, Not Reviewed"
        }
    });
}

I realized that I was overcomplicating my flow by using a range in another sheet, because the source in the datavalidation set rule, requires the values in a comma-delimited string, so instead of using another range/sheet and complicating my flow, I just wrote the values myself in the source part.

I also realized that by converting the sheet to a table, it was easier to determine the range of the column (this part came from the stackoverflow post).

Anyway, thank you again! I leaving my post as the solution, but kudos to you for helping me.