- Create a Service Account
- 1. Create a Google Developer Account
- 2. Create a Google Developer Project
- 3. Enable the Google Sheeets API
- 4. Creating a Service Acccount
- Linking the Google Sheets Document
- 1. Create the Google Sheets Document
- 2. Share the Google Sheets Document
- 3. Record Relevant Information
- The Spreadsheet ID
- The Sheet Name
- The Grid ID
Note: If you are only going to read from the Google Sheets document, and notedit or write data to the document, you can skip the process of making a ServiceAccount. The only requirement is that the Google Sheets document is shared suchthat anyone with the link can read the document.
In this tutorial, we will be creating a Service Account, a user account that’screated to interact with a specific piece of software or service. In our case,this specific piece of software is the Google Sheets API. You can then reusethis Service account for other apps made with App Inventor.
1. Create a Google Developer Account
The first step to use the Google Sheets API is to create a Google DeveloperAccount. A Google Developer Account is a Google Account that is activated to usedeveloper tools, so if you have not already done so, begin by navigating tohttps://www.google.com and create a google account. Once you have done so,navigate to https://developers.google.com and sign in to your desired GoogleAccount. This does not need to be the google account which will own the GoogleSheets that you intend to use.
2. Create a Google Developer Project
Our next step is to create a coding project. Once you have logged into yourGoogle Developer Account, navigate to https://console.developers.google.com/. Ifthis is your first time using your Google Developer Account, you will beprompted to agree to the Terms of Service. Check the box and continue.
Next, you will be on the Developer Dashboard. If you have not created a projectbefore, you can create your first project by selecting [CREATE PROJECT].
If you are using an existing Developer Account and already have a Project, youwill be on a Project’s Dashboard. To create a new Project, click on the dropdown menu at the top of the webpage.
At the top of the pop-up menu, select NEW PROJECT.
On the Creation page, give the project a name. For this tutorial, we will nameit “App Inventor.” We don’t need to provide an organization, so we will leave itas No organization.
Click ‘Create’ to finish creating the project.
3. Enable the Google Sheeets API
The next step is to enable the Google Sheets API for your project. Click on the“+ Enable APIs and Services” button next to APIs & Services.
Enter in the search bar “Google Sheets API” and select the resulting API thatappears. Then click the Enable button for the API Library.
4. Creating a Service Acccount
Now that we have enabled the Google Sheets API for this project, we want tocreate a service account. Simply put, this service account will look a lot likea google email account. After selecting the ‘Enable’ button for the GoogleSheets API, you will be sent to the Overview tab for the Google Sheets API inyour project. To the right of the screen, click ‘Create Credentials’ to getstarted with creating the Service Account.
On the next page, we are presented with a guide that will ask various questionsabout what kind of project we are making. In the first drop down menu under‘Which API are you using?’, select the Google Sheets API.
After you select that, a follow-up question will appear below. Under thequestion “Where will you be calling the API from?”, select Other UI. Finally,under what data will you be accessing, select Application Data.
After entering the above information, click on the blue button labeled “Whatcredentials do I need?” This will take you to the second part of creating thecredentials. This part will allow you to customize the service account andshould look like the figure below.
The name of the service account is not important. For this tutorial, I will nameit “example-service.” You then set the role to Selecting Project > Editor, whichshould provide this service account with enough permissions. Make sure to savethe entire text in the box labeled Service Account ID. You should notice thatthis will look a lot like an email address. You will want to copy this ServiceAccount ID someplace so that we can use it later.
Make sure that “JSON” is selected under Key Type like it is above beforepressing ‘Continue’. This will download a JSON file with the name“AppInventor-####.json.”
When using the Google Sheets API component, we will upload this json file inorder to work properly. Make sure to keep this JSON safe!
Now that we have created a Service Account, we want to provide the service account access to the Google Sheets files we will or read from.
1. Create the Google Sheets Document
The first step, if you haven’t already, is to create a Google Sheets document.The name of the Google Sheets document can be anything you’d like. If youalready have a Google Sheets file you would like to use, then simply go to yourGoogle Drive and navigate anywhere you would like to create the document. Thenclick New and select New Sheet.
After you have created the Google Sheets file that you would like to use, yousimply need to share the Google Sheets file with the service account email wesaved from earlier. This will provide the service account with the permissionsyou need. You can specify whether the service account can only read the data orwhether the service account can read and write the data by changing thepermissions between Viewer and Editor respectively.
3. Record Relevant Information
Finally, there are a handful of pieces of information about the google sheetsdocument which you would need to record in order to properly access the file.You can also use this portion of the instructions as a reference for where tofind the information later.
You can read more about these terms here.
The Spreadsheet ID
The spreadsheet ID can be found in the url of the Google Sheets Document. The url of the Google Sheets Document is typically in the form:
https://docs.google.com/spreadsheets/d/<Spreadsheet ID>/edit#gid=<Grid ID>
The spreadsheet ID corresponds to the ID of the Google Sheets File.
The Sheet Name
Within a spreadsheet are individual sheets, each of which have unique titles and ID numbers. In this component, the title of the page is called the sheetName.
A single spreadsheet document can have multiple pages. By default, when youcreate a new Google Sheets document, the spreadsheet has a single page with thename “Sheet1”. You can find this in the tabs at the bottom left corner of thescreen like so:
You can add a new page to the document by pressing the “+” symbol. Doing sowill create a new tab next to Sheet1 that will read Sheet2.
Each block in the Google Sheets Component will ask you to specify the title of the page (or the “Sheet Name”) you want to read from or edit. For example, if I want to edit values on Sheet2, then I would provide the text “Sheet2” in the sheetName slot.
Additionally, you can rename the sheet to whatever you’d like by right clickingon the tab and clicking rename. Note, doing so would require that you changethe sheet name in the project as well to match.
The Grid ID
Similarly to the sheet name, this is used to refer to which page you would liketo edit. However, unlike the sheet name, the grid ID cannot be customized bythe user. Certain blocks require that the grid ID is provided instead of asheet name. To find the grid ID, navigate to the page you want to reference byclicking on the tab. In the URL, you should see that the end should have “gid=”followed by a number.
https://docs.google.com/spreadsheets/d/<Spreadsheet ID>/edit#gid=<Grid ID>
Unable to resolve host "oauth2.googleapis.com"
If you get this error message, double check your device’s connection to theinternet. This is a sign that the Android networking subsystem can’t resolvethe hostname, so it’s not able to talk to DNS.
Attempt to invoke virtual method … on a null object reference.
If you get this error message, double check that you have linked yourcredentials in the designer properties. If your Designer property reads“None…” like it says in the image below, click on the box and select the .jsonfile you got from the Service Account setup steps.
Unable to parse range: ______
If you get this error message, there may be an error with the range that youprovided. This could mean that the sheetName you’ve provided does not actuallyexist, or that the reference you provided is not valid A1-notation. To learnmore about A1 notation, see this reference:https://developers.google.com/sheets/api/guides/concepts#a1_notation
