Google Sheets Downloader

Google Sheets Downloader is a component of the data preparation and distribution pipeline (see Data Preparation and Distribution Pipeline). Google Sheets Downloader downloads data from a Google Sheets spreadsheet. You can either specify the source spreadsheet directly by its ID or point to the Google Drive folder where the spreadsheet is stored.

Source Spreadsheet Requirements

Make sure that your source Google Sheets spreadsheet meets the following requirements:

• The source spreadsheet has one or more sheets with data.
• In each sheet:
  • The first row is a header.
  • Each column with data has a name.
  • The names of the columns contain only ASCII characters, preferably in lowercase and separated by underscores (not spaces).

Configuration File

Creating and setting up the brick’s configuration file is the third phase of building your data pipeline. For more information, see Build Your Data Pipeline.

For more information about configuring a brick, see Configure a Brick.

Minimum Layout of the Configuration File

The following JSON sample is the minimum layout of the configuration file that Google Sheets Downloader can use. If you are using more bricks, you can reuse some sections of this configuration file for other bricks. For example, the ‘downloaders’ section can list more downloaders, and the ‘integrators’ and ‘ads_storage’ sections can be reused by other downloaders and executors (see Configure a Brick).

This sample describes the use case of one instance of Google Sheets Downloader that downloads two sheets from a Google Sheets spreadsheet.

Copy the sample and replace the placeholders with your values.

You can later enrich the sample with more parameters depending on your business and technical requirements (see Customize the Configuration File).

{
  "entities": {
    "table_1_name": {
      "global": {
        "custom": {
          "hub": ["column_1_name","column_2_name","column_3_name"]
        }
      }
    },
    "table_2_name": {
      "global": {
        "custom": {
          "hub": ["column_1_name","column_2_name","column_3_name"]
        }
      }
    }
  },
  "downloaders": {
    "gsheets_downloader_id": {
      "type": "gs",
      "entities": ["table_1_name","table_2_name"]
    }
  },
  "gs": {
    "client_id": "google_client_id",
    "spreadsheet_id": "spreadsheet_id"
  },
  "integrators": {
    "ads_integrator_1": {
      "type": "ads_storage",
      "entities": ["table_1_name","table_2_name"]
    }
  },
  "ads_storage": {
    "instance_id": "data_warehouse_instance_id",
    "username": "dw_email@address.com",
    "options": {}
  }
}

The placeholders to replace with your values:

"entities": {
  "company": {
    "global": {
      ...
    }
  }
}

Customize the Configuration File

You can change default values of the parameters and set up additional parameters.

Describe the structure of the source Google Sheets spreadsheet

You can set the parameters describing the structure of the source Google Sheets spreadsheet.

"gs": {
  "client_id": "google_client_id",
  "spreadsheet_id": "spreadsheet_id",
  "options": {
    "files_structure": {
      "escape_as": "escape_character", // optional
      "db_parser": "gdc|{no_value}", // optional
      "enclosed_by": "quote_character" // optional
    }
  }
}

Specify additional load options for the entities

For each entity, you can set up additional load options.

"entities": {
  "table_1_name": {
    "global": {
      "custom": {
        "hub": ["column_1_name","column_2_name","column_3_name"],
        "skip_rows": number_of_rows, // optional
        "db_parser": "gdc|{no_value}" // optional
      }
    }
  }
}

Add multiple instances of Google Sheets Downloader

When you use only one instance of Google Sheets Downloader, it reads its remote location parameters from the ‘gs’ section.

If you use multiple instances of Google Sheets Downloader and each instance has a separate location to download data from, add several ‘gs’ sections and give each section a unique name (for example, ‘gs_users’ or ‘gs_sales’).

"gs_users": {
  "client_id": "google_client_1_id"
},
"gs_sales": {
  "client_id": "google_client_2_id"
}

For each instance of Google Sheets Downloader in the ‘downloaders’ section, add the ‘settings’ parameter and set it to the name of the section that contains settings for this Google Sheets Downloader instance.

"downloaders": {
  "gsheets_downloader_1_id": {
    "type": "gs",
    "settings": "gs_users",
    "entities": ["table_1_name","table_2_name"]
  },
  "gsheets_downloader_2_id": {
    "type": "gs",
    "settings": "gs_sales",
    "entities": ["table_1_name","table_2_name"]
  }
}

Point to a folder containing the source Google Sheets spreadsheet

By default, Google Sheets Downloader looks for a specific source Google Sheets spreadsheet to download data from (see the ‘spreadsheet_id’ parameter in Minimum Layout of the Configuration File). If the source spreadsheet does not have a constant ID (for example, the spreadsheet is re-generated from time to time and its ID changes), point to a folder where the source spreadsheet is stored instead of specifying the spreadsheet ID. This way, you do not rely on the spreadsheet ID but rather on its location.

"gs": {
  "client_id": "google_client_id",
  "folder": "google_drive_folder_id"
},

Add optional parameters

Depending on your business and technical requirements, you can set one or more optional parameters affecting the data load process.

"gs": {
  "client_id": "google_client_id",
  "spreadsheet_id": "spreadsheet_id",
  "full": true|false, // optional
  "value_render_option": "format" // optional
}

Customize integrators

The ‘integrators’ and ‘ads_storage’ sections define the behavior of integrators.

"integrators": {
  "ads_integrator_1": {
    "type": "ads_storage",
    "entities": ["table_1_name","table_2_name"]
  }
},
"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {}
}

If you want to add more parameters or change the defaults, see ADS Integrator.

Schedule Parameters

When scheduling Google Sheets Downloader (see Phases of Building the Data Pipeline -> Production Implementation), provide the parameters from this section in the schedule.

General Parameters

Some parameters must be entered as secure parameters (see Configure Schedule Parameters).

Some parameters contain the names of the nesting parameters, which specifies where they belong in the configuration hierarchy. For example, a parameter entered as gs|options|password is resolved as the following:

"gs": {
  "options": {
    "password": ""
  }
}

Enter such parameters as is (see Schedule Example).

Schedule Example

The following is an example of how you can specify schedule parameters: