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.
   
   Description of the illustration create-client.png
   
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.
   
   Description of the illustration new-client.png
   
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.
     
     Description of the illustration auth-continue.png
     
   • 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.
   
   Description of the illustration add-connection.png
   
3. Selecting Add Connection opens an Add Connection wizard in the Connections wizard's connection list panel.
   
   Description of the illustration add-connection-fields.png
   

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:
   
   Description of the illustration implicit.png
   

   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:

   
   
   Description of the illustration explicit.png
   

   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.
   
   Description of the illustration export.png
   
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.
   
   
   Description of the illustration adb-google-wizard.png
   
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.