Articles in this section

Google Sheets

Avatar
Adverity Solutions Consulting
  • Updated
Follow

INTRODUCTION

With this integration, you can either:

  • Write data to an existing spreadsheet: Choose which sheet to write to (gid parameter)
  • Create a completely new spreadsheet: New file 'Sheet1' will be created and written to (gid=0)
Note: due to the nature of Google Sheets, traditional overwriting based on date is not currently possible, instead Mode: Truncate and Mode: Append are supported.
  • Mode: Truncate - The most recent extract will always replace the entire existing content.
  • Mode: Append - The most recent extract will always be appended (added at the bottom) of existing content.

Since both these options are not practical for daily data that can be subject to retroactive changes, the recommended option is to create a Bundle stream first, which collects all daily extracts from a Datastream and then imports it using Mode: Truncate.

  • The API stream (e.g. Facebook Ads) should be set to the necessary time range, e.g. "Last 7 Days".
  • The Bundle stream should be set to the necessary time range, e.g. "This Year" or "Fixed" + no end.
  • The API stream (e.g. Facebook Ads) should be set to retain extracts by date.
  • The Bundle stream should be set to retain extracts by fetch, and only retain 1. 

Alternatively, Mode: Append could be used for append-only data, where each new extract will be added to the existing data set.

 

CREATE A DESTINATION

  1. In the Transfer Element, Click "+Add" in the top right.
  2. In the list of Destinations, select "Google Sheets" under the File/Datalake heading.
  3. Click "Setup a new Connection".
  4. Use your Google account credentials to login and authorize Adverity to access data.
  5. Select Mode: Truncate (Default) or Mode: Append.
  6. Optional: Define an output folder.
  7. Define a name for a new spreadsheet or choose an existing spreadsheet and sheet.
  8. Save to finalize the Connection, your Google Sheets data will now be available to the Adverity platform.mceclip0.png

 

SCHEMA MAPPING

  • By default, you are prompted to use Schema Mapping. You can use the defaults or create your own Data Schema.
  • If no schema mapping is needed, this option can be deselected as well.

 

SHEET NAMES

Note: Each Datastream will automatically generate a separate sheet in the destination. The default name of the output file is datastreamtype_datastream-id e.g. adwords_83. A hyperlink to the sheet will appear as well next to the name with the first 8 characters of the sheet ID.
  1. Navigate through Transfer > Destinations and select your Google Sheets Destination from the list.
  2. In the settings, select the Datastream in the left column and write a Sheet Name in the right column.
    • Allowed characters for Sheet Name are "a-z", "A-Z", "0-9",  and "_".
    • You can use the placeholders in the right column to configure dynamic sheet names:
      • {id} - Datastream ID
      • {app_label} - Datastream Type Short Name
      • {name} - Actual Extract's name
      • %Y - Year (YYYY)
      • %m - Month (MM)
      • %d - Day (DD)

Related articles

Comments

0 comments

Article is closed for comments.