Oracle Cloud Infrastructure Documentation

Install and setup the add-on for Google Sheets

Before you install the Oracle Autonomous Database add-on for Google Sheets, download the oracleGoogleAddin zip file from your Database Actions instance.

  • Open the Database Actions Launchpad.
  • Under the Downloads tab, select the DOWNLOAD MICROSOFT EXCEL/GOOGLE SHEETS ADD-IN pane.
    Description of download-add.png follows

  • This opens a Download screen with Microsoft Excel and Google Sheets tabs. Click the Google Sheets tab and select Download Add-in.
    Description of download-add-google-sheet.png follows

You can now view a zip file in the your system's Downloads folder. Extract the contents of the zip file onto your system.

To set up the Oracle Autonomous Database add-on for Google Sheets, import the files in the oracleGoogleAddin folder to Google Apps Script.
Note

Importing the files is a one-time activity, and typically, this is done by an administrator.

After you import or upload the files to Google Apps Script follow these steps to complete the setup of the Oracle Autonomous Database add-on for Google Sheets:

Parent topic: Oracle Autonomous Database for Google Sheets

Upload oracleGoogleAddin files to Google Apps Script using Clasp

To upload all the files present in the oracleGoogleAddin folder, you must use the Command Line Apps Script Project (clasp).

Clasp is an open-source tool to develop and manage the Google Apps Script projects from your terminal.
Note

Clasp is written in Node.js. and distributed via the Node Package Manager (NPM) tool. It is required to install Node.js version 4.7.4 or later to use clasp.
  1. Enter sheet.new in the web browser's address bar to open Google Sheets. Make sure you are logged in with your Google account.
  2. Select Apps Script from the Extensions menu. You can view the Apps Script editor window.
    Description of apps-script-window.png follows

  3. Select the Code.gs file in the Apps Script editor window, which already exists by default. Click on the vertical dots beside the Code.gs file. Select Delete to delete the existing Code.gs file.
  4. After you install Node.js, enter the following npm command in the command prompt to install clasp. You must enter this command in the location where you have downloaded and extracted the oracleGoogleAddin folder.
    C:\Users\username\Desktop\oracleGoogleAddin>npm install @google/clasp -g

    To run the command as an administrator for UNIX- and Linux-based systems, enter the following command:

    sudo npm install @google/clasp -g

    After you install Clasp, the command is available from any directory on your computer.

  5. Enter the following command to log in and authorize managing your Google account’s Apps Script projects.
    clasp login
    Once this command is run, the system launches the default browser and asks you to sign into your Google account where your Google Apps Script project will be stored. Select Allow for clasp to access your Google Account.
    Note

    If you have not enabled the Apps Script API in Google Apps Script, the above command will not be successful. Enable the API by visiting the https://script.google.com/home/usersettings site and allow site and enabling the Google Apps Script API by selecting the On button.
  6. In your existing Google Apps Script project, click the Project Settings in your left pane. Click Copy to Clipboard to copy the Script ID under IDs.
  7. Go back to the command prompt and enter the following command with the Script ID you copied in the previous step as displayed in the image below:
    clasp clone <Script ID>
  8. Push all the files from your folder to the Google Apps Script files by specifying the following command:
    clasp push

    This command uploads all of the script project's files from your computer to Google Apps Script files.

  9. Go to the newly created Google Sheet, click the Extensions menu, and select Apps Script. Under Files, you can view all the files in the oracleGoogleAddin folder.
  10. After you import or upload the files to Google Apps Script, follow these steps to complete the set up of the Oracle Autonomous Database add-on for Google Sheets:

Deploy the Google script as a web app

After all the files from the oracleGoogleAddin folder are imported or uploaded to the Google Apps Script files, you must deploy the Google script as a web app.

To deploy the Google script as a web app:
  1. Click on the Extensions menu in the Google Sheet you are working on and select Apps Script. This opens the window.
  2. Click Deploy button on the top right and select New deployment. A New deployment window opens.
  3. Next to Select type, click the settings icon and select Web app.
  4. Under Configuration, specify a Description of the deployment in the Description field. For example, Web app deployment.
  5. Under Web app , select the Google account you used to log in from the Execute as drop-down. Optionally, you can choose anyone who has access to this deployment.
  6. Select Deploy.
    Note

    • If you receive a window that asks you to Authorize access, select it. This will redirect you to the Google Accounts page where you must to select your Gmail account.
    • Click Advanced and select the Go to Untitled project (unsafe) link.
    • Selecting the link opens a new window, ensuring that you trust the application. Click Allow to continue.

    If you Authorize access at this stage, you need not follow steps 2-4 in the Authorize Google Sheets to use Autonomous Database.

  7. Click Done to close the New Deployment window.
  8. Click the Deploy button on the top right and select Manage Deployments.
  9. On the Manage Deployments page, you can view a Web app URL. Use the Copy to Clipboard to copy the Web app URL. For example, here is a sample of the web app URL "https://script.google.com/macros/s/AKfycbwFITvtYvGDSsrun22g7TrbrfV-bUVoWKs7OrA_3rtRAlmcGFe8bejNprZML7gFPzQ/exec". This is the Web application deployment URL.

  10. Save this URL, which you will use later in the Google Sheet Redirect URL field when downloading a connection file from Database Actions or manually creating a connection from the Google Sheet to the Autonomous Database.

    For details on selecting Response Type, see Download Connection File.

  11. You can close the Apps Script browser tab and navigate to the Google Sheets browser tab. You are now ready to create a connection to the Autonomous Database.
  12. Ensure you save the worksheet after uploading all the files to Apps Script. Click the Refresh button once you have uploaded all the files. You can now view a new Oracle Autonomous Database menu in the Google Sheets.
    Description of ask-oracle-menu.png follows

    Note

    Generate OAuth Client ID and OAuth Client Secret fields by using the UI.