Send Data to Google Sheet from MIT App Inventor

Hello friends and welcome to Obsidian Soft,

In today’s MIT App Inventor tutorial, I will teach you a simple and easy way of writing to a Google sheet from an MIT App Inventor App. 

Some apps require data to be written to a spreadsheet instead of a database like CloudDB. Take the example of a quiz app that you have developed for your students (I have already taught you how to make quiz apps). Now, you may want to know how your students are performing in the quiz so you can write code in your quiz app that whenever a person takes the quiz, the score is written to a Google sheet which you can view at any time to look at the scores of the students.

So, today, I will teach you how your app can easily write to a Google sheet.

First of all, we need a Google sheet so create one by going to:

https://docs.google.com/spreadsheets/u/0/

Click on the big + and create a blank Google sheet. A new spreadsheet will open. Change the name from “Untitled Spreadsheet” on top to “Students”. Now go to the menu. You will find Tools there. Click on it and choose the “Create a new form” option.

We need to change the questions to the kind of data coming in from the MIT App Inventor app. In this tutorial, I will be sending students' names and ages. If you have an email address in it by default chosen in it, you need to remove that question by going to settings ->defaults ->form defaults ->Do not collect email address

Look below at the screenshot

Now, go back to questions and add questions for name and age.

For both questions, choose a short answer as the answer type. 

At any time, if you accidentally close the form, you can open it again for editing by going to Tools in your "Students" spreadsheet menu

Tools -> Manage Form -> Edit form

Now, create a new project in MIT App Inventor. Call it StudentData.

Drag and drop a horizontal arrangement from Layouts onto the Viewer.  Make align horizontal and align vertical center. Make height 10% and width fill parent. Put a label and textBox from User Interface inside it. This is for the student’s name so name them appropriately: nameLbl and nameTxt. Duplicate the horizontal arrangement by pressing Ctrl  + C and Ctrl + V for Windows OR Command + C and Command +  V for MacOS. Make changes for Age.

Add a button for submit from User Interface.

From Connectivity, drag and drop a Web component onto the viewer.

Our basic screen design is done so go to the blocks section.

When submitBtn is clicked, we want to send the user-entered data to the Google sheet so get the click event.

Get web1’s seturl block. And put a join in it. Add three more slots in it.

Now, follow me very carefully.

Go back to the form that you created using Spreadsheet. If you accidentally closed it, you can open it again by going to Tools in your spreadsheet

Tools -> Manage Form -> Edit form

Inside your form, you will have those 3 dots next to send button. Click on those dots and choose Get prefilled link.


Another browser window will open up. Look at the web address in the newly opened browser window and copy it and paste it into the first part of your join in seturl block in a text block.


Replace the word prefill with the word formResponse. Type it exactly in this way:

formResponse

Now, we need something more from the form.

Go back to the form and provide some values for name and age and press getlink button.


Copy and paste the new link in some text file as we need the two entry codes for both inputs i.e. name and age.
We will get something like this (I have deleted some letters from this address as this points to a Google Sheet on my account: you will have to use your own Google Sheet for your project)

https://docs.google.com/forms/d/e/1FAGycfw/viewform?usp=pp_url&entry.1932012884=obsidian&entry.1402638227=100

Copy and paste the first information about entry &entry.1932012884= in the second slot

Replace & by ?:

This replacement only has to be done for the first entry and not for subsequent entries. (Your code will be different. This is just for example)

In the third slot, get the nameTxtBox.text block (whatever user has entered as the name)

In the fourth slot, copy and paste the information about second entry i.e. &entry ( numbers from link)=

In the fifth slot, get the ageTxtBox.text block (whatever user has entered as the age)

At the end, call web’s get procedure.

Try the app, and you will see that data is added in the spreadsheet in a sheet called form responses 1 inside your main spreadsheet Students.

I hope you liked this class and found this method of writing data to a Google Sheet super easy. 

You can have a look at the video here:




Please like my videos and share it with your friends and family. Also, subscribe to my channel and press the bell icon so you don't miss any of the great projects I have planned for you.


Please like my Facebook page and Instagram page for more educational resources and tips.



Pinterest:
https://www.pinterest.co.uk/obsidiansoft/_created/
For links to free educational apps, have a look at the educational apps page

Comments

  1. SouthPaaw9 November 2024 at 00:44

    This is a great tutorial, thank you. I seem to be having one problem. The data will upload fine, but then the next time it will fail. Once it worked once, failed once, worked twice, the failed continuously. I'm not sure if the issue is in my app, my phone, my spreadsheet, or the linked form. Is this anything you have seen before? Thank you again for all of your great tutorials.

    ReplyDelete
    Replies
    1. Obsidian Soft11 November 2024 at 22:50

      Hello. Thank you for liking the tutorial. As you are saying that the upload worked at least once then it doesn't seem to be a problem with the app. It can be a problem with the internet so confirm that your network is working fine. This is the simplest way of adding data to the spreadsheet using an MIT App Inventor app but it has its shortcomings. If you will manually delete data from rows in your spreadsheet and then start adding data again through the app, it will start adding after the manually deleted data. So, if you want to start with a clean sheet, delete the rows and not just the data inside them. Also, make sure that you are looking at the correct sheet.

      Delete
  2. Lukee10 December 2024 at 06:42

    Hey, I can't get the data to send to the google sheet. I am using a tablet to test the app and I'm not sure if that's part of the problem but I'm not too sure what to do.

    ReplyDelete

Post a Comment

Popular posts from this blog

Jolly Phonics Group 1

Image
Free Jolly Phonics Group 1 worksheets (s, a, t, i, p, n) According to Oxford Dictionary, phonics is a "method of teaching people to read by correlating sounds with symbols in an alphabetic writing system". In simple words, each letter of the alphabet has a sound. Instead of teaching young children the names of these alphabets, the sounds are taught. For example "a" makes the sound /a/...this is exactly the sound that comes from your mouth in the beginning when you say the word " apple " or " ant ". The letter "a" makes a different sound in words like " arm " and " ate " but these rules are taught later.  Jolly Phonics is a synthetic phonics system where all the letters of the alphabet have been split into seven groups. Each group contains 6 letters or their combinations so a total of 42 sounds are taught. What do I mean by a combination of letters? Remember that the combination of letters "s" and "h...
Read more

Jolly Phonics Group 2

Image
Free Jolly Phonics Group 2 worksheets (c/k, e, h, r, m, d) Hello Friends, I am back with another set of worksheets for helping children learn their phonics sounds. As you might already know, I am a strong proponent of the Jolly Phonics system of synthetic phonics sounds. Jolly Phonics system follows a particular order of teaching alphabet sounds in which there are seven groups. The following image shows the seven groups of Jolly Phonics along with an extra group taught by my Phonics app on Google Play Store. My app called  Phonics Fun  is 100% Free so you can download it and use it on all your Android devices.  It teaches all the letter sounds and different phonics rules and follows the pattern of Jolly Phonics but it is available for free. It is the perfect way to master phonics as you can hear phonics sounds and also test learning by playing many matching games. Coming back to printable Jolly phonics worksheets, I have already published worksheets for practicing...
Read more

Jolly Phonics Flash Cards

Image
Gone are the days of A for apple, B for Ball...this is the time of synthetic phonics where each alphabet has a sound. Now, you don't teach children the name of the letter but the sound made by the letter. A has the sound that comes when we say the word apple, b has the sound that comes when we say bed, and so on and so forth. There are different systems of synthetic phonics but I have felt that Jolly Phonics is the best system. In fact, I made my first mobile app for my son based on the Jolly Phonics style of teaching. However, I know that many people want to limit screen exposure for their children so I decided to make these flashcards.  You can use these flashcards independently or you can use them along with my app called Phonics Fun , available on the Google Play Store. You can hear the sounds from my app. Practice using these flashcards and then, test learning using my app too...a win-win situation :) The Jolly Phonics flash cards are double-sided. I have told you in my previ...
Read more