Hello Andro Devs
Today I’m going to show you how to use Spreadsheet Component
Google Sheets API Setup
Create a Service Account
To use the Google Sheets API, you will need to create a Service Account. A service account is a user account that’s created to interact with a specific
The first step to use the Google Sheets API is to create a Google Developer Account. A Google Developer Account is a Google Account that is activated to use developer tools, so if you have not already done so, begin by navigating to https://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 Google Account. This does not need to be the Google account that will own the Google Sheets that you intend to use. piece of software or service. The service we are using in this case is the Google Sheets API.
Our next step is to create a coding project. Once you have logged into your Google Developer Account, navigate to https://console.developers.google.com/. If this is your first time using your Google Developer Account, you will be prompted 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 project before, you can create your first project by selecting [CREATE PROJECT].
If you are using an existing Developer Account and already have a Project, you will be on a Project’s Dashboard. To create a new Project, click on the drop-down 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 name it “Android Builder.” We don’t need to provide an organization, so we will leave it as No organization.
Click ‘Create’ to finish creating the project.
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 that appears. Then click the Enable button for the API Library.
Then click on enable to use the API
Now that we have enabled the Google Sheets API for this project, we want to create a service account. Simply put, this service account will look a lot like a Google email account. After selecting the ‘Enable’ button for the Google Sheets API, you will be sent to the Overview tab for the Google Sheets API in your project. To the right of the screen, click ‘Create Credentials’ to get started with creating the Service Account.
Check these configurations and click on Done then Create Credentials from Create Service Account option
Maybe the upper screenshot doesn’t show for everyone If it’s not visible then Create Credentials from here
On the next page, The name of the service account is not important. For this tutorial, I will name it “example-service.” Make sure to save the entire text in the box labeled Service Account ID. You should notice that this will look a lot like an email address. You will want to copy this Service Account ID somewhere so that we can use it later. after that click on the Create and Continue buttons.
In the next step, you need to select a role of the service account to Editor
After completing all steps click on DONE and move back to the dashboard then find your service account and click on the edit button.
after clicking on the edit button find the KEYS Section in the tab bar.
then click on ADD KEY and choose Create New Key to create an access file.
After choosing the Create New Key option Choose JSON Key type and continue on create and download it to use the key file in Android Builder
Link the Google Sheets Document
Now that we have created a Service Account, we want to provide the service account access to the Google Sheets files we will read from.
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 you already have a Google Sheets file you would like to use, then simply go to your Google Drive and navigate anywhere you would like to create the document. Then click New and select New Sheet.
After you have created the Google Sheets file that you would like to use, you simply need to share the Google Sheets file with the service account email we saved from earlier. This will provide the service account with the permissions you need. You can specify whether the service account can only read the data or whether the service account can read and write the data by changing the permissions between Viewer and Editor respectively.
Enter your service account email here
After that select EDITOR and send the request.
Finally, there are a handful of pieces of information about the Google Sheets document that 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 to find the information later.
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:
A single spreadsheet document can have multiple pages. Typically, when you create a new Google Sheets document, the spreadsheet has a single page with the name “Sheet1”. You can find this in the tabs at the bottom left corner of the page. It should look something like this.
You can add a new page to the document by pressing the “+” symbol. Doing so will create a new tab next to Sheet1 that will read Sheet2.
Whenever you want to edit one page or the other, you will need to specify the Sheet Name, which is the text you see in the tab. For example, if I want to edit values on Sheet1, then I would provide the text “Sheet1” when the block prompts for the SheetName.
Additionally, you can rename the sheet to whatever you’d like by right-clicking on the tab and clicking rename. Note, doing so would require that you change the sheet name in the project as well to match.
Similarly to the sheet name, this is used to refer to which page you would like to edit. However, unlike the sheet name, the grid ID stays the same and cannot be changed by the user. Certain blocks require that the grid ID be provided instead of a sheet name. To find the grid ID, navigate to the page you want to reference by clicking on the tab. In the URL, you should see that the end should have “gid=” followed by a number. For Sheet 1, this value should be 0, as in the example link above. For new pages you create, this will be a much longer number.
Building the App
Connect to the Android Builder website and start a new project. Name the new project [According to you], set the screen’s orientation to Portrait, and uncheck the Screen’s scrollable property [If enabled]. You may also wish to set the Screen’s Title property to something other than ‘Screen 1’.
1. Designing the App
In addition to the Google Sheets component, the app makes use of several other types of components. Use the designer window to create the interface for your application.
Upload your Credentials JSON File
Upload your Credentials JSON File
When you first created the Service Account, you should have been able to download the credentials as a JSON file, named something like “demo-####.json.” The Spreadsheet component will need this file to work properly. In the designer, upload this JSON file you downloaded from the Google Developer Console by clicking on the Upload File button under Media, the same way you would upload an image file.
After you have uploaded the JSON file, click on the Spreadsheet1 component and find the “CredentialsJson” property. Click where it says “None…” under credentialsJson and select the JSON file you previously uploaded.
& Always try to add a JSON file from the block section to work properly.
Save the Spreadsheet ID
In addition to the JSON file, you’ll see that you also need the spreadsheet ID. You can find the spreadsheet ID in the URL of the Google Sheets document we’ve created earlier, where the URL should be in the form:
Remember that the Google Sheets document needs to be shared with the Service Account already. Copy the spreadsheet ID and paste it into the designer component labeled “spreadsheet”. Or you can directly add your spreadsheet URL too.
2. Blocks Editor
Additional Blocks Range
A Note About Error Messages
There are a few different reasons your app may get a response from the Google Sheets component that gives an Error related to Parsing or an Invalid Query. To get the error message, you can read the error message by using the ErrorOccured event block and displaying the errorMessage. I would suggest using the notifier component to display the error message whenever one occurs, as in this image.
Unable to resolve host “oauth2.googleapis.com”
If you get this error message, double-check your device’s internet connection.
Attempt to invoke virtual method … on a null object reference.
If you get this error message, check that you have linked your credentials in the designer properties. If your Designer property reads “None…” or the block section doesn’t have credentials blocks, click on the box and select the JSON file you’ve uploaded previously. else set the block and add JSON file.
Unable to parse range: ___
If you get this error message, there may be an error with the range that you provided. This could mean that the Sheet Name you’ve provided does not actually exist, or that the range is not in proper A1-Notation. To learn more about A1 Notation, see this reference.
Now that you have a simple app that uses Spreadsheet Component
If you want, you can watch a tutorial on YouTube: https://youtu.be/hBbNdE2Fvn0
Thanks for reading Any suggestions or are you facing any bugs? then ask or post here to resolve it.
Team Kapx innovation