Introduction

Google Sheets plugins will allow users to import data from Google Sheets into their pipeline, so that they can transform and enrich with other data sources.

User Storie(s)

• As a pipeline developer, I want to import data from Google Sheets, so that I can transform and enrich it using CDAP
• As a pipeline developer, I want to move all sheets from a given Google drive directory to a destination
• As a pipeline developer, I want to be able to pick certain sheets from a particular sheet to process using CDAP, so that I do not have to process all sheets all the time. I want to be able to specify the sheet using the sheet name or number.
• As a pipeline developer, I want to treat the first row of a sheet as a header, so that CDAP can automatically treat it as schema
• As a pipeline developer, I want to be able to specify a section at the top of my Sheet as a header, so that it is extracted as metadata, and not as actual data
• As a pipeline developer, I want to be able to specify a section at the bottom of my sheet as a footer, so that it is extracted as metadata, and not as actual data

Plugin Type

•  Batch Source
•  Batch Sink 
•  Real-time Source
•  Real-time Sink
•  Action
•  Post-Run Action
•  Aggregate
•  Join
•  Spark Model
•  Spark Compute

Configurables

Source

This section defines properties that are configurable for this plugin. 

Note: The data in the specified header and footer rows should not be available as records to the rest of the pipeline. It should be stored as metadata. 

Sink

Note: Incoming records should be written to columns in the sheet

Design / Implementation Tips

Source

• One structure record per sheet's row.
• Single spreadsheet file per split.
• Schema is defined by data from sheets.
• Custom column names should not be empty, empty columns are skipped.
• Row numbers are counted from 1.

Sink

• Single file per input structure record.

Design

Approach(s)

Source

Google Sheets Source plugin reads spreadsheet files from specified Google Drive folder via Sheets API.

Each structure record represents single row of some sheet from spreadsheet, also it may contain metadata from metadata rows the same for all data rows of the sheet.

The plugin separates each input spreadsheet file into separate split.

The out scheme is auto-generated by plugin. It mandatory contains spreadsheet name, sheet title and set of column values, also there can be a metadata record which is optional. Data formats for cells are getting from the first data row of the first suitable sheet of the first spreadsheet which was found. In the case the first data row is empty the data formats will be set to String by default.

First data row is defined as first one after custom column names row or after last header row.

Last data row is bounded by one of the following values (the lowest is used):

• Last data row property value set by user;
• the row before First footer row (when is set);
• last row of the sheet (1000 is the default value, may be greater).

Data types transforming:

* CDAP does not have any logical type for time intervals (Scheme.LogicalType.TIME_MILLIS/TIME_MICROS supports only intervals less than a day), so long type is used for number of milliseconds for time of a day/time interval.

Sink

Properties

Security

Limitation(s)

Future Work

• Some future work – HYDRATOR-99999
• Another future work – HYDRATOR-99999

Test Case(s)

• Test case #1
• Test case #2

Sample Pipeline

Please attach one or more sample pipeline(s) and associated data. 

Pipeline #1

Pipeline #2