Forum Discussion

Mike_Collins999's avatar
Mike_Collins999
Copper Contributor
Jun 03, 2024

Pre-Fill Responses not being recognised / applied

Hi All,    Not sure if this is the best forum but wanted to raise it so it doesn't get overlooked.  The issue was highlighted in the comments to the blog post Pre-fill Responses in Your Microsoft F...
Microsoft Forms
EP-UIS's avatar
EP-UIS
Copper Contributor
I'm also having the same problem. I haven't figured out a workaround yet. It sounds like our use cases are similar. I agree that a power app or power page would solve this, but I think you have to pay for extra licensing if you want an external user or public user to access a power app. I'm sending a request to a customer where they can type in a PO number, and I need some sort of unique reference so that I can correlate it back to the request so that I get the right PO number against the right project / work order / invoice. If I come up with a workaround, I'll let you guys know. I don't necessarily need MS Forms, since I just want a way to send an email for someone to fill in the blanks.
EP-UIS's avatar
EP-UIS
Copper Contributor
Jun 13, 2024

I figured out how to do this with an Excel file with a table within it with protection added so that they can only modify certain columns.

 

Perhaps this will work for you:

 

I created an Excel file with a table in it and I'm using it like a template (although stored as a workbook).

 

Within the "template" file I have a table called Table, and I have columns that are intended for reference info that will be read-only for the end user (written to as part of this flow) and columns for the external public user to edit.

 

I use a Copy File action to duplicate it and give it a unique name.

 

I add rows to the table within the new excel file.

Since I'm working with a copied file, I'm using the Id output of the Copy a File action for the file reference.  Because of this, the Add a Row Into a Table action will not pull the list of tables and the list of columns, but you can manually specify these by typing the name of the table exactly and specifying the column data like this with exact matches for column names:

{
"Proposed Invoice Number": "INV-9999",
"Description": "Blah blah blah 9999 blah",
"Subtotal (before tax)": "123.45",
"Request Date": "",
"Status": "Waiting on PO number"
}

Columns that I want them to enter data into don't need to be listed above and they'll default to blank.  I've added conditional formatting to highlight the cells red when they're blank for the columns I want them to fill in.

 

I use an office script to lock the columns that I don't want the customer to edit, and I unlock the columns that I do want them to edit.  Script is below.  I'm using LockRange of A:E and UnlockRange of F:G.  You can easily customize.

 

I create a sharing link with Edit enabled.

 

I send the email with the link.

 

Then I can use a flow to detect the change in a file in the folder so that I can then process the change.

 

Here is the Office Script for locking the excel file, which can easily be called from Power Automate:

 

function main(workbook: ExcelScript.Workbook, password: string, lockRange: string, unlockRange: string) {
  // Get the active worksheet
  let worksheet = workbook.getActiveWorksheet();

  // Unprotect the worksheet if it's already protected
  if (worksheet.getProtection().getProtected()) {
    worksheet.getProtection().unprotect(password);
  }

  // Set the entire sheet to be locked
  worksheet.getUsedRange().getFormat().getProtection().setLocked(true);

  // Unlock the specified range
  let rangeToUnlock = worksheet.getRange(unlockRange);
  rangeToUnlock.getFormat().getProtection().setLocked(false);

  // Lock the specified range
  let rangeToLock = worksheet.getRange(lockRange);
  rangeToLock.getFormat().getProtection().setLocked(true);

  // Protect the worksheet with the specified settings
  worksheet.getProtection().protect({
    allowAutoFilter: false,
    allowDeleteColumns: false,
    allowDeleteRows: false,
    allowFormatCells: false,
    allowFormatColumns: false,
    allowFormatRows: false,
    allowInsertColumns: false,
    allowInsertHyperlinks: false,
    allowInsertRows: false,
    allowPivotTables: false,
    allowSort: false
  }, password);
}

Resources