DS Trix Addon

Disclaimer: This is not an official Google product.

Overview

DS Trix Addon is an addon for Google Sheets which allows you to sync Web Query Reports from DoubleClick Search directly into a Google Sheets spreadsheet. To use this addon, you will need to have access to DoubleClick Search.

The addon has the following features:

• Download data from web query reports directly into spreadsheet (instead of downloading CSV and re-uploading to the sheet).
• Scheduled updates which allows the addon to sync the data and update your spreadsheet even if the sheet is not open or being used.
• Do ad-hoc refreshes of the data.

You can use additional tabs in the same spreadsheet to create a dashboard based on your reports.

Installation

1. Download all the files in this repository to your computer.

2. Open Google Drive in a browser window. If you don’t have a Google account, create a new account. Else, login with the account you wish to use.

3. Create a new spreadsheet using New > Google Sheets.

4. Give your spreadsheet a name by clicking on the title at the top.

5. Open the script editor by going to Tools > Script Editor ...

6. Give your script a name (Ex: DS Trix Addon).

7. You should have the file Code.gs open in the editor. On your computer, open Code.gs from the files you downloaded in step #1 in a local text editor.

8. Copy the contents of the entire file into the script editor, replacing whatever is already present in the editor.

9. Press ctrl/cmd-s or go to File > Save to save the file.

10. You will need to add all the remaining source files to the same project while retaining the names exactly as they are for the files you downloaded (Note: The names are case sensitive).

    1. For files that end with .html, you need to create an HTML file by going to File > New > Html file.
    2. For file that end with .gs, you need to create a script file by going to File > New > Script file.
    3. Ignore the files LICENSE, README.md, CONTRIBUTING.md. These are not needed for the addon.
    4. For every file, simply copy the contents of the file from your computer to the editor replacing any existing content.
    5. Make sure to save all the files after copying the contents.

11. The final step is to configure OAuth so that the addon can access your Web Query Reports on DoubleClick Search.

    1. In the script editor, open the file Constants.gs. This file has all the configuration parameters that we will populate.
    2. In the script editor, go to Resources > Cloud Platform Project.
    3. A dialog should pop up with a link to the cloud project that this script is associated with. It will be of the form <Project Name> - project-id-1234567890123456789. Click on this link to open the associated cloud project.
    4. If the left side menu is not open, click the Menu button at the top left of the page. It should have three horizontal lines.
    5. In the menu click on APIs & services > Dashboard.
    6. In the dashboard, click on + ENABLE APIS AND SERVICES.
    7. In the search box enter DoubleClick Search API. The search results should list the API entry. Click on it to open.
    8. At the top of the new screen, click on ENABLE.
    9. On the menu currently open on the left side of the page, click on Credentials (Under APIs & services).
    10. In the list of client ids, you will see an entry for Apps Script. Click on it to open it.
    11. You will see the client id and client secret at the top. Copy each of these and paste them in the Constants.gs file at the points where it says insert_client_id and insert_client_secret. After pasting, save the file.
    12. In the script editor, go to File > Project properties. Copy the value next to Script ID.
    13. In Constants.gs, paste the script id in the variable value for REDIRECT_URI at the place where it says insert_script_id. Save the file.
    14. Copy the entire URL for REDIRECT_URI. Go back to the Cloud Console browser window and in the same page that we copied the client id and client secret from, paste the url under Authorized redirect URIs. Click outside the text box and click on Save. You can close the cloud console page.
    15. In the script editor, go to Resources > Libraries. In the text box at the bottom of the dialog, paste this value - 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF and click on Add.
    16. You will see the OAuth2 library added in the dialog. In the version dropdown, select 21 as that’s the version this addon was built with. Once done click on Save.

12. Now we can start using the Addon. Close the script editor and refresh the spreadsheet. When it loads, you will now be able to see the the menu entry under Addons > <your script name>. Under this menu, click on Set web query URL to start using the addon.

13. When using for the first time, you will see dialogs asking for authentication and permissions. These will be asked only the first time for each user. Follow the instructions on them.

14. When setting up authentication, you may see a message that says This app isn't verified. If you see this, click on Advanced and select Go to <name of script>. In the pop up, type in Continue and press Next to proceed.

    Update (12-Apr-2018):

    Recent changes with the way scopes are handled by apps script are causing the addon to fail for new users. To overcome this, the scopes need to be explicitly specified in the manifest file (appsscript.json). This file has been added to the repo. It needs to be added to the addon by opening the script editor and going to View > Show manifest file and copy pasting the contents there`. Once updated, refresh the spreadsheet and try to re-authenticate. If necessary, purge the properties by using the addon menu.