Mass Affiliation - Develop web form for service providers to input account number to link managed businesses to

[fareenr]

Create a web form for each law firm to input a single new BC Registry account number to link the managed businesses to that account.

Main question for recipients to input is 'Please provide the new BC Registry account number that you would like to manage all business registration records with this admin email address'

• Account numbers must be max 5 digits
• Text to confirm agreement that BC Registries can migrate all businesses with that admin email address to that new account
• Approver can input contact information: email address, first name, last name, their account name

The google form/survey must be a unique web link for each law firm, pre-populated with their company /admin email address.

Provide option to download list of businesses that currently use that admin email address. The list will be generated through a different task (23079 or related task). This solution will need to link to or call a google sheet saved in separate view-only google drive folders).

This task should also include the ability to Send out the email from GCNotify (if possible) - must be no reply email. A mail merge solution may be needed to send out over 1000 emails with a unique google survey/form link for each lawfirm/service provider with a google document link or downloadable csv file for each client.

[forgeuxGH5]

@fareenr can you add some context about the requirements here - it would be helpful to know how the law firms have their information about businesses stored right now - is it in Excel (or some other spreadsheet app)? I thought they were using ALF and Atheenian - do those apps have export functionality and if so, what do those output files look like (format, file type, etc.)?

[fareenr]

@forgeuxGH5 Each law firm has their own system, many use Athennian or ALF or some have their own corporate system for all of their business contracts. So, each law firm's output report could look completely different. We are not planning to ask each law firm for their list of managed businesses. Whatever they have in COLIN affiliated to their company admin email address will be migrated over based on current COLIN registry data.

[forgeuxGH5]

Do you have a list of law firms (with email address) or are you planning on sending an email to all unique emails listed as contact email in COLIN?

[fareenr]

@forgeuxGH5 The prelim list based on COLIN data of admin email addresses affiliated to 50 or more corporations is linked in ticket #23079 as part of the epic.

[fareenr]

27-Sep - reviewed draft webform for Sameer and identified issues with the Administrator Email Address which should be populated with the admin email address from the source datasheet from COLIN. The individual email address is for the contact submitting the form.

[sameer0422]

function createFormsFromSpreadsheet() { // Replace this with your spreadsheet URL or ID var spreadsheetUrl = 'Replace with your URL';

// Extract the Spreadsheet ID from the URL var spreadsheetId = spreadsheetUrl.match(/\/d\/([a-zA-Z0-9-_]+)\//)[1];

// Open the spreadsheet using the ID var spreadsheet = SpreadsheetApp.openById(spreadsheetId);

// Get the first sheet (or change to your sheet name) var sheet = spreadsheet.getSheets()[0];

// Get all data from the sheet var data = sheet.getDataRange().getValues();

// Loop through each row (starting from row 2 assuming the first row is headers) for (var i = 1; i < data.length; i++) { var adminEmail = data[i][0]; var corpNumber = data[i][1];

// Create a new form
var form = FormApp.create('Managed Business Request Form (' + corpNumber + ')')
    .setDescription('Corporate Online will be replaced with the new BC Registry application.\n\n' +
                    'Submit this form to request migration of the business registration records which you have authority to manage.\n' +
                    'The current business registration record will be migrated from Corporate Online in to the BC Registries & Online Services account that you request based on the administrator email address that you provide.\n\n' +
                    'Note that if any updates are needed to the existing business registration record including company or admin email address, these must be completed in the business Corporate Online registration record.\n\n' +
                    'If you need to create a new account, go to www.bcregistry.gov.bc.ca.');

// Set form properties to meet the required conditions
form.setCollectEmail(false) // Ensure "Collect email addresses" is NOT set to "Verified"
    .setAllowResponseEdits(false) // Disable "Allow response editing"
    .setLimitOneResponsePerUser(false) // Disable "Limit to 1 response"
    .setRequireLogin(false); // Disable "Restrict to users in our domain and its trusted organizations"

// Add email question
var emailItem = form.addTextItem()
  .setTitle('Email')
  .setRequired(true);
var emailValidation = FormApp.createTextValidation()
  .setHelpText('Please enter a valid email address.')
  .requireTextMatchesPattern('^[\\w\\.-]+@[\\w\\.-]+\\.[a-zA-Z]{2,}$') // Regex to validate email
  .build();
emailItem.setValidation(emailValidation);

// Add contact name question
form.addTextItem()
  .setTitle('Contact Full Name (first name and last name)');

// Add account name question
form.addTextItem()
  .setTitle('Account Name - Managing Organization\'s Name');

// Add admin email question
var adminEmailItem = form.addTextItem()
  .setTitle('Administrator Email address for existing managed business registration records')
  .setRequired(true);
adminEmailItem.setValidation(emailValidation);

// Add help text (no response needed for this item)
form.addSectionHeaderItem()
  .setTitle('You MUST provide your new BC Registries & Online Services application account number that all business registration records with this company email address will be migrated to.\n' +
            'This is a 4-5 digit number which is not the same as your BC OnLine account number. To find your new account number, login to the BC Registries application and view it under Account Settings > Account Info > Account Number.');

// Add a question for the user to input the account number with validation for max 5 digits
var accountNumberItem = form.addTextItem()
    .setTitle('BC Registries & Online Services Account Number(s) (4-5 digits)')
    .setRequired(true);

// Apply validation to ensure only digits and a max length of 5
var accountNumberValidation = FormApp.createTextValidation()
    .setHelpText('Account Number must be 5 digits or less.')
    .requireTextMatchesPattern('^\\d{1,5}$') // Regex to allow only digits, 1-5 characters
    .build();

accountNumberItem.setValidation(accountNumberValidation);

// Create a form response to pre-fill adminEmail
var formResponse = form.createResponse();
formResponse.withItemResponse(adminEmailItem.createResponse(adminEmail));

// Generate a pre-filled URL for the form
var preFilledUrl = formResponse.toPrefilledUrl();

// Log the pre-filled URL for the form
Logger.log('Pre-filled Form URL: ' + preFilledUrl);

} }

[sameer0422]

https://docs.google.com/forms/d/e/1FAIpQLSeWT8zzlaeAueUc2fSMhU5o0qs1q3s3OpNm7n0XjbJ5Sfpr6g/viewform?entry.144680102=0819840@gmail.com