Initial Setup The following steps need to be followed before replicating the Apps Script;

1. Forwarding Gov Form Submissions Set up your Gov Form submissions to be forwarded to your personal Gmail address as they are submitted. This can be done either directly or through a Google Group.
2. Set Up Labels in Gmail Create two labels in your Gmail account: ○ Unprocessed – For new form submissions. ○ Processed – For processed submissions.
3. Create a Gmail Filter Set up a Gmail filter to automatically apply the Unprocessed label to all form submissions: ○ Use the subject line and sender address to target the correct emails. ○ Select "Skip Inbox" and "Mark as Read" to keep your inbox uncluttered. ○ The Unprocessed label will be accessed by the Apps Script for processing.
4. Create a Dedicated Google Drive Folder Create a folder in Google Drive to store all form submissions. ○ Set up a separate folder for each different Gov form you wish to process.
5. Set Up a Google Sheet Inside the new folder, create a Google Sheet to track submissions: ○ Name it something like "Form Submissions (GSheets)" — the name isn’t critical for script functionality. ○ This sheet will store a list of all received submissions.

Apps Script Once the initial setup has taken place, you can create the scripts. 6. Set Up Apps Script ○ Open the Google Sheet created in Step 5. ○ Go to ‘Extensions’ > ‘Apps Script’. 7. Enable Required APIs ○ In the Apps Script window, select ‘Services’ from the left panel. ○ Add both the Gmail API and Drive API and sign in if prompted.

8. Create and Add Extract_Emails script ○ Create a new script file named ‘Extract_Emails.gs’: ○ Copy and paste the provided script into this file. ○ This script will extract data from emails and create a new Google Sheet
9. Create and Add Update_Sheet script ○ Create a second script file named ‘Update_Sheet.gs’: ○ Copy and paste the provided script into this file. ○ This script will update the main Google Sheet with a list of all form submissions plus the URLs to the Google Sheets containing each submission. ○ This script is triggered at the end of the ‘Extract_Emails’ script but can also be ran independently if needed.

Change the Variables The ‘Extract_Emails’ script contains three variables which need to be changed for each setup. 10. Update Script Variables Open the ‘Extract_Emails.gs’ script and update the following variables:

■ labelUnprocessed – Set this to the name of the Unprocessed label created in Gmail. ■ labelProcessed – Set this to the name of the Processed label created in Gmail. ■ destinationFolder – Set this to the folder ID of the Google Drive folder created in Step 4 (where form submissions will be stored). Open the ‘Extract_Emails.gs’ script and update the following variable: ■ destinationFolder – Set this to the folder ID of the Google Drive folder created in Step 4.

Testing the Scripts 11. Test the ‘Extract_Emails’ Script ○ In the Apps Script window, select ‘Extract_Emails.gs’. ○ Click ‘Run’ to execute the script. ○ If prompted, sign in to your Google account and grant the necessary permissions. 12. Verify Processing ○ Check that the emails have been processed correctly. ○ Confirm that the new Google Sheets have been created and populated with the extracted data.

○ Ensure that the ‘Unprocessed’ label is removed and replaced with the ‘Processed’ label in Gmail.

Set a Trigger 13. Create a Time-Driven Trigger ○ In the Apps Script window, go to ‘Triggers’ (clock icon on the left panel). ○ Click ‘+ Add Trigger’ at the bottom right. ○ Set up the trigger as follows: ■ Function to run: Extract_Emails ■ Event source: Time-driven ■ Type of time-based trigger: Choose an appropriate schedule (e.g., every 5 minutes, hourly, daily) ○ Click ‘Save’ and authorize if prompted.