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
KyleTurner's avatar
KyleTurner
Copper Contributor
I am experiencing this as well, but oddly it's specific to the form: I have one form where this problem occurs, and another similar where it works as intended. I don't see any difference in settings between the forms.

Choosing "Clear Form" from the top-right menu allows the next pre-filled link to work, but then that response persists.
EP-UIS's avatar
EP-UIS
Copper Contributor
Jun 13, 2024
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