Setting a database with smart forms

General
1

See here how you can use Google App Script to store form data as they are collected in your Gmail dynamically, in order to build a database.

6 Likes
2

@correspondent-y This could be handy for something I’m doing just now. I’ll try this out and see how it goes. I’d also like to be able to fetch this data back and output it to a webpage. It’s to update the Carp records for our fishing club. :smiley:

3

Would be happy to get your feedback after implementing it.

4

https://blog.kuligaposten.com/articles/google-sheet-as-database

2 Likes
5

with the data on the spreadsheet, It should be possible to embed the spreadsheet on a webpage, I do not know if that solves your problem

6

I’ll mess about with them and see how it goes. There’s no rush. Cheers :+1:

7

If you are able to set up the script to work and collect your smart form data into the spreadsheet. On the spreadsheet navigate to File > Share > Publish to web > Choose embed, you can actually embed it in so many forms like pdf, excel, etc etc… There are so many setting you can set, you can choose who can see and who cannot, and which parts of the sheet to show and not show, just browse the settings once you are on ‘publish to web’. And as the sheet data changes, it dynamically changes on the webpage as well.

1 Like
8

It’s a great script. I noticed that if some of the fields in the form are optional and the user didn’t enter any data into them, the received email doesn’t have those ‘Name’ attributes. For example, if phone number in your form wasn’t filled by the user, this line would give odd data:
var email = extractField(emailBody, 'Email', 'Tel');

One solution could be that we look for that optional data and in its absence, set the extractField’s 3rd argument as the next attribute.

if (emailBody.indexOf('Tel') !== -1) {
  email = extractField(emailBody, 'Email', 'Tel');
}
else
{
  email = extractField(emailBody, 'Email', 'Preferred Date For Examination:');
}

Additionally, it might sometimes be important to clear the list of processed emails, for example if we want the script to recheck the already processed emails. I added a quick script to do this:

function removeMessageFromProcessed(messageId) {
  var scriptProperties = PropertiesService.getScriptProperties();
  var processedMessageIds = getProcessedMessageIds();
  Logger.log("processed ids are: " + processedMessageIds);

  // Find the index of the messageId to remove
  var indexToRemove = processedMessageIds.indexOf(messageId);

  // If found, remove it from the list
  if (indexToRemove !== -1) {
    processedMessageIds.splice(indexToRemove, 1);

    // Update script properties
    scriptProperties.setProperty('processedMessageIds', processedMessageIds.join(','));
  } else {
    Logger.log("Message ID " + messageId + " not found in processed list.");
  }
}

To call this, use:

removeMessageFromProcessed(messageId);

before this if statement:

if (!processedMessageIds.includes(messageId)) {

The script really helped in doing something I have been wanting to for a long time. Thank you so much @correspondent-y.

9

Thanks very much for this input. I am now realizing if processed emails are not cleared as you are saying, with time as the form gets more inputs, the execution time of the script will be very long.

10

Upon second thought, clearing the processed emails would mean deleting them from your inbox as well, since, they will be repopulated on the sheet if they are not.