Using Google sheets and Google Forms to create a certificate verification portal

Share on email
Share on linkedin
Share on reddit
Share on facebook
Share on telegram

With recent completion of my “15 days GIS project based course” with good success, I had to distribute certificates for the same to learners. So, I planned to create a verification portal for verification of digital course completion certificates as digital certificates can be easily duplicated. I had to strictly pursue free and long-lasting approach as the verification portal has to last for years to come. Survey123 of ArcGIS supposedly does this easily, but it was not a free service so alternatives were to be found.

Check out the verification portal: https://ioespotlight.wordpress.com/verification/

This verification portal is powered by embedding Google sheets and Google forms pages into WordPress site of Spotlight club webpage which is also limited but free. All of these services have lasted for decades, so they will also probably sustain without future maintenance and at free of cost.

First of all, I created a regular google form (forms.gle) which is more or less easy to make. The form consists of optional entry of name, email and a compulsory entry of verification token.

Now after creation of the form and publish of the same, an excel sheet can be created from within the form which will be created with same name in ./My Drive/ folder of Google Drive.

The google form and the excel sheet now can be moved to a new folder for permanent storage. After all this, the excel file can be renamed to self convenience and can be opened. Some Dummy data is filled within the google form, so that it shows up in Sheets. The columns in the sheet is filled up accordingly.

Now the original data for checking the records is also added in a new worksheet. A random number generator is created so as to give students individual tokens. I wanted to provide a 10 digit token so function = RANDBETWEEN(1000000000, 9999999999) was executed and individual students were assigned the token that they can use for that course as shown below.

Original dummy values

Using these data, I will have to create a verification form that looks like the one displayed below.

Sample data I want to display

Now all I have to do a VLOOKUP check if the input check data and the certificate verification data in DataClasses sheets where data is stored matches and create an output.

= VLOOKUP ( input token, output token table, name of student, 2 )

This above function gives the output if the token matches the record. Now a custom pattern can be created to display in the output alongside, using string concatenation.

If the token matches a data in the record,

output = Salutation, Name, has attended our GIS class which was conducted from , start date to end date. He/ She (using if conditionals) has received a participation certificate for the class.

If token does now match any data:

output = The token does not match any of our records, so you should check if there are any gaps.

Now the output can be published, to create embed html text.

Google sheets : Publish files as web pages – Google Workspace Learning Center

Google forms: Make Google Docs, Sheets, Slides & Forms public – Computer – Docs Editors Help

Now using these two embed links, we can create a wordpress static page and create a page that looks like this.

Then the embed HTML links were pasted into “Custom HTML” panel.

  • Google Sheets embed link in the right
  • Google forms embed link in the left

Then the page is published and distributed.

Hope this will prove useful for other fellow creators for certificate verification.

Leave a Comment

Your email address will not be published. Required fields are marked *