To connect to the Autonomous Database, you can download a connection file from the Database Actions instance and import it to the Google Sheet add-on you have setup.

In this section you use the Web UI to obtain the client_id and client_secret .

You generate the client keys by accessing the Autonomous Database instance URL appending with oauth/clients.

For example, if your instance is “ https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/_sdw/", you need to sign in to the link " https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<schema Name>/oauth/clients/". Be sure to include the trailing slash.

1. Sign in to Database Actions with the "https://machinename.oraclecloudapps.com/ords/SchemaName/oauth/clients/" link. You can view an OAuth Clients page in the link "https://localhost:port/ords/schemaName/_sdw/?nav=rest-workshop&rest-workshop=oauth-clients".
2. Click the +Create OAuth Client button to create a new client.
   
   
   
3. From the Grant type drop-down, select the type of client connection you want. You can select the following options:
   • AUTH_CODE: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.
   • IMPLICIT: Select this option for explicit connection. This is the more secure method and is preferred to use if the Autonomous database has public access.
4. Enter the following fields. The fields with an asterisk (*) are mandatory:
   • Name: Name of the client.
   • Description: Description of the purpose of the client.
   • Redirect URI: web application deployment URL you copied from step 10 of Deploy the Google Script as a Web app
   • Support URI: Enter the URI where end users can contact the client for support. Example: https://script.google.com/
   • Support Email: Enter the email where end users can contact the client for support.
   • Logo: Optionally, select an image from your local system to insert a logo for your new client.
   Navigate to the Roles tab to select the roles of the client. This is not a mandatory field.
5. Progress to the Allowed Origins tab. Specify and add the list of URL prefixes in the text field. This is not a mandatory field.
6. Progress to the Privileges tab to add any privilege. You are not required to have any privileges to create an OAuth Client.
7. Click Create to create the new OAuth Client. This registers the OAuth Client which you can view on the OAuth Clients page.
   
   
   
8. Click the show icon to view the Client ID and the Client Secret fields.

How do I connect manually?

The following sections demonstrate how to connect using implicit and explicit connections. Google Sheets needs permission to access the Autonomous Database. You must first complete the authorization to connect to the autonomous database. The add-on requires one-time authentication for the setup.

1. On the Google Sheet, click Oracle Autonomous Database and select Connections.
   Selecting Connections requires one-time Google authentication.

   • Clicking Connections opens a pop-up window that asks your permission to run the authorization. Click Continue.
     
     
     
   • You will now view a window that informs you that the application requests access to sensitive information in your Google account.
   • Click Advanced and select the Go to Untitled project (unsafe) link. Selecting the link opens new window, ensuring you trust the application. Click Allow to continue. You have now completed the setup.
2. On the Connections wizard, click Add Connection from the Manage Connections drop-down menu to add a connection.
   
   
   
3. Selecting Add Connection opens an Add Connection wizard in the Connections wizard's connection list panel.
   
   
   

4. Specify the following field values in the wizard:

   Connection Name: Enter the connection's name—for example, TestConnection.

   Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. For example, “https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/”

   In the OAuth Client Grant Type field, select one of the two options based on the type of connections you want. Refer to the Generate Client ID and Client Secret using the UI section.

   This option varies with implicit and explicit connections.

   Implicit: Select this option for implicit connection. Use this response type when the autonomous database is in a private subnet or within a customer firewall.

   AUTH_CODE: Select this option for explicit connection. This is the more secure method and is preferred to use.

   When you select the Implicit option, you can view the following fields:
   
   
   

   OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

   Schema Name: Specify the name of the schema.

   When you select AUTH_CODE, you can view the following fields:

   
   
   
   

   OAuth Client ID: client_id you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

   OAuth Client Secret: client_secret you generate using the Create New Client wizard in the UI. Refer to the Generate Client ID and Client Secret using the UI section.

   Schema Name: Specify the name of the schema.

   Click Save.

   After you click Save, you can view the new connection in the connection list panel. The connection list displays the connection's name, the schema's name, and the OAuth type you grant. However, it is still in a disconnected state.

5. Click the three vertical dots beside the connection name and perform the following operations:

   Connect: Select Connect to the Autonomous Database and change the connection status to active. Selecting Connect opens the sign-in page of the Autonomous database. After you log in, you will view a page that shows that database access has been granted to you. Close the window and return to Google Sheets. You will now see that the connection is active.

   Edit: Select Edit to update any value of the connection. Click Save to update the edited values.

   Duplicate: Select Duplicate to create a duplicate connection.

   Remove: Select Remove to remove the connection from the connection list.

Exporting Connections

1. Click Export Connection from the Manage Connections drop-down menu to export the selected connection.
2. Select the connection you want to export, and click Export.
   
   
   
3. Click Export.

4. The exported connection downloads in your local system. The connection file is saved as spreadsheet_addin_connections.json.

After your identity is determined using OAuth authentication, Google Sheets needs permission to access the Autonomous Database.

The client_id and client_secret values you generate during OAuth authentication are used for authorization.

1. Click on the Oracle Autonomous Database menu in the Google Sheet you are working on and select Register. This requires one-time Google authentication.
2. Clicking Register opens a pop-up window that asks your permission to run the authorization. Click Continue. Selecting Continue will redirect you to the Google Accounts page, where you must select your Gmail account.
3. You will now view a window that informs you that the application requests access to sensitive information in your Google account. Click Advanced and select the Go to Untitled project (unsafe) link.
4. Selecting the link opens a new window, ensuring you trust the application. Click Allow to continue.
5. You have now completed the setup. Select Register from the Oracle Autonomous Database menu in the Google sheet.
   This opens an Oracle Autonomous Database wizard in the Google sheet. Specify the following fields:

   • ADB URL: Enter the ADB URL. For example, "https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/ords/<Schema Name>".
   • OAuth Client ID: client_id you generate during authentication.
   • OAuth Client Secret: client_secret you generate during authentication. Refer to the Create Connections with the Google spreadsheet section for more details.
   
   
   
   
6. Select Authorize.

After successfully authorizing the credentials, you can view Connections, Direct SQL, Data Analysis, Analyses and Reports Clear Sheet, Delete All Sheets, About Autonomous Database, and Sign Out menu items under Oracle Autonomous Database.

The Oracle Autonomous Database add-on for Google Sheets lets you run SQL queries to work with your data in a Google Sheet. With the add-on, you can type your SQL code in the SQL editor area and click Run to run the command.

The add-on loads the result in the Google Sheet. The time taken to load the results depends on the number of records and the complexity of the query.

You can view Reports and Analytic Views or visualize data for analysis purposes.

The reports and charts can be viewed in various charts: Bar Charts, Area charts, Line Charts, and Pie Charts. Reports provide analytical insights that you create from the Analytic Views. An Analysis can contain multiple reports. The Analyses and Reports icon enables you to retrieve Analyses and Reports from the Autonomous Database.

Once the add-on runs the query and retrieves the data into the worksheet, you can view the Timestamp, User, AV-query and SQL-query of the Analytic View in the automatically generated query results.

Once the add-on runs the query and retrieves the data into the worksheet, you can view the Timestamp, User, AV query, and SQL query of the Analytic View in the automatically generated query results.

The worksheet displays the result of the query in one go. Consider, for example, if you want to modify the query and generate the query result in the same sheet. You must clear the existing data in the sheet.

To clear query results in the Google sheet, click the menu item Oracle Autonomous Database and select Clear Sheet.

This option erases all data types in the selected sheet, including images and formatting.

Use this option to delete all the sheets existing in the spreadsheet.

Select Delete All Sheets from the Oracle Autonomous Database menu to delete all sheets from the spreadsheet.

Use this option to view details about the add-in

The About Oracle Autonomous Database menu from Oracle Autonomous Database displays if the add-on is connected to server, the ORDS version, the Add-in version, the ORDS Schema version, the database major and minor version, the ADB URL and the Schema.

Once you generate the query results in the Google Sheet, you can share it with other users. With sharing, creates a copy of the worksheet and sends it with the design tools hidden and worksheet protection turned on.