3 from collections
import OrderedDict
5 from singer
import metadata
6 from tap_google_sheets
.streams
import STREAMS
8 LOGGER
= singer
.get_logger()
11 # https://github.com/singer-io/getting-started/blob/master/docs/DISCOVERY_MODE.md#Metadata
13 # Convert column index to column letter
14 def colnum_string(num
):
17 num
, remainder
= divmod(num
- 1, 26)
18 string
= chr(65 + remainder
) + string
22 # Create sheet_metadata_json with columns from sheet
23 def get_sheet_schema_columns(sheet
):
24 sheet_title
= sheet
.get('properties', {}).get('title')
25 sheet_json_schema
= OrderedDict()
26 data
= next(iter(sheet
.get('data', [])), {})
27 row_data
= data
.get('rowData', [])
28 # spreadsheet is an OrderedDict, with orderd sheets and rows in the repsonse
30 headers
= row_data
[0].get('values', [])
31 first_values
= row_data
[1].get('values', [])
32 # LOGGER.info('first_values = {}'.format(json.dumps(first_values, indent=2, sort_keys=True)))
36 'additionalProperties': False,
38 '__sdc_spreadsheet_id': {
39 'type': ['null', 'string']
42 'type': ['null', 'integer']
45 'type': ['null', 'integer']
50 header_list
= [] # used for checking uniqueness
55 # Read column headers until end or 2 consecutive skipped headers
56 for header
in headers
:
57 # LOGGER.info('header = {}'.format(json.dumps(header, indent=2, sort_keys=True)))
59 column_letter
= colnum_string(column_index
)
60 header_value
= header
.get('formattedValue')
61 if header_value
: # NOT skipped
62 column_is_skipped
= False
64 column_name
= '{}'.format(header_value
)
65 if column_name
in header_list
:
66 raise Exception('DUPLICATE HEADER ERROR: SHEET: {}, COL: {}, CELL: {}1'.format(
67 sheet_title
, column_name
, column_letter
))
68 header_list
.append(column_name
)
72 first_value
= first_values
[i
]
73 except IndexError as err
:
74 raise Exception('NO VALUE IN 2ND ROW FOR HEADER ERROR. SHEET: {}, COL: {}, CELL: {}2. {}'.format(
75 sheet_title
, column_name
, column_letter
, err
))
77 column_effective_value
= first_value
.get('effectiveValue', {})
80 if column_effective_value
== {}:
81 column_effective_value_type
= 'stringValue'
82 LOGGER
.info('WARNING: NO VALUE IN 2ND ROW FOR HEADER. SHEET: {}, COL: {}, CELL: {}2.'.format(
83 sheet_title
, column_name
, column_letter
))
84 LOGGER
.info(' Setting column datatype to STRING')
86 for key
, val
in column_effective_value
.items():
87 if key
in ('numberValue', 'stringValue', 'boolValue'):
88 column_effective_value_type
= key
90 elif key
in ('errorType', 'formulaType'):
92 raise Exception('DATA TYPE ERROR 2ND ROW VALUE: SHEET: {}, COL: {}, CELL: {}2, TYPE: {}, VALUE: {}'.format(
93 sheet_title
, column_name
, column_letter
, key
, col_val
))
95 column_number_format
= first_values
[i
].get('effectiveFormat', {}).get(
97 column_number_format_type
= column_number_format
.get('type')
99 # Determine datatype for sheet_json_schema
101 # column_effective_value_type = numberValue, stringValue, boolValue;
102 # INVALID: errorType, formulaType
103 # https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ExtendedValue
105 # column_number_format_type = UNEPECIFIED, TEXT, NUMBER, PERCENT, CURRENCY, DATE,
106 # TIME, DATE_TIME, SCIENTIFIC
107 # https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#NumberFormatType
109 column_format
= None # Default
110 if column_effective_value
== {}:
111 col_properties
= {'type': ['null', 'string']}
112 column_gs_type
= 'stringValue'
113 LOGGER
.info('WARNING: 2ND ROW VALUE IS BLANK: SHEET: {}, COL: {}, CELL: {}2'.format(
114 sheet_title
, column_name
, column_letter
))
115 LOGGER
.info(' Setting column datatype to STRING')
116 elif column_effective_value_type
== 'stringValue':
117 col_properties
= {'type': ['null', 'string']}
118 column_gs_type
= 'stringValue'
119 elif column_effective_value_type
== 'boolValue':
120 col_properties
= {'type': ['null', 'boolean', 'string']}
121 column_gs_type
= 'boolValue'
122 elif column_effective_value_type
== 'numberValue':
123 if column_number_format_type
== 'DATE_TIME':
125 'type': ['null', 'string'],
126 'format': 'date-time'
128 column_gs_type
= 'numberType.DATE_TIME'
129 elif column_number_format_type
== 'DATE':
131 'type': ['null', 'string'],
134 column_gs_type
= 'numberType.DATE'
135 elif column_number_format_type
== 'TIME':
137 'type': ['null', 'string'],
140 column_gs_type
= 'numberType.TIME'
141 elif column_number_format_type
== 'TEXT':
142 col_properties
= {'type': ['null', 'string']}
143 column_gs_type
= 'stringValue'
145 # Interesting - order in the anyOf makes a difference.
146 # Number w/ multipleOf must be listed last, otherwise errors occur.
161 column_gs_type
= 'numberType'
162 # Catch-all to deal with other types and set to string
163 # column_effective_value_type: formulaValue, errorValue, or other
165 col_properties
= {'type': ['null', 'string']}
166 column_gs_type
= 'unsupportedValue'
167 LOGGER
.info('WARNING: UNSUPPORTED 2ND ROW VALUE: SHEET: {}, COL: {}, CELL: {}2, TYPE: {}, VALUE: {}'.format(
168 sheet_title
, column_name
, column_letter
, column_effective_value_type
, col_val
))
169 LOGGER
.info('Converting to string.')
171 column_is_skipped
= True
172 skipped
= skipped
+ 1
173 column_index_str
= str(column_index
).zfill(2)
174 column_name
= '__sdc_skip_col_{}'.format(column_index_str
)
175 col_properties
= {'type': ['null', 'string']}
176 column_gs_type
= 'stringValue'
177 LOGGER
.info('WARNING: SKIPPED COLUMN; NO COLUMN HEADER. SHEET: {}, COL: {}, CELL: {}1'.format(
178 sheet_title
, column_name
, column_letter
))
179 LOGGER
.info(' This column will be skipped during data loading.')
182 # skipped = 2 consecutive skipped headers
183 # Remove prior_header column_name
184 sheet_json_schema
['properties'].pop(prior_header
, None)
185 LOGGER
.info('TWO CONSECUTIVE SKIPPED COLUMNS. STOPPING SCAN AT: SHEET: {}, COL: {}, CELL {}1'.format(
186 sheet_title
, column_name
, column_letter
))
192 'columnIndex': column_index
,
193 'columnLetter': column_letter
,
194 'columnName': column_name
,
195 'columnType': column_gs_type
,
196 'columnSkipped': column_is_skipped
198 columns
.append(column
)
200 sheet_json_schema
['properties'][column_name
] = col_properties
202 prior_header
= column_name
205 return sheet_json_schema
, columns
208 # Get Header Row and 1st data row (Rows 1 & 2) from a Sheet on Spreadsheet w/ sheet_metadata query
209 # endpoint: spreadsheets/{spreadsheet_id}
210 # params: includeGridData = true, ranges = '{sheet_title}'!1:2
211 # This endpoint includes detailed metadata about each cell - incl. data type, formatting, etc.
212 def get_sheet_metadata(sheet
, spreadsheet_id
, client
):
213 sheet_id
= sheet
.get('properties', {}).get('sheetId')
214 sheet_title
= sheet
.get('properties', {}).get('title')
215 LOGGER
.info('sheet_id = {}, sheet_title = {}'.format(sheet_id
, sheet_title
))
217 stream_name
= 'sheet_metadata'
218 stream_metadata
= STREAMS
.get(stream_name
)
219 api
= stream_metadata
.get('api', 'sheets')
220 params
= stream_metadata
.get('params', {})
221 querystring
= '&'.join(['%s=%s' % (key
, value
) for (key
, value
) in \
222 params
.items()]).replace('{sheet_title}', sheet_title
)
223 path
= '{}?{}'.format(stream_metadata
.get('path').replace('{spreadsheet_id}', \
224 spreadsheet_id
), querystring
)
226 sheet_md_results
= client
.get(path
=path
, api
=api
, endpoint
=stream_name
)
227 # sheet_metadata: 1st `sheets` node in results
228 sheet_metadata
= sheet_md_results
.get('sheets')[0]
230 # Create sheet_json_schema (for discovery/catalog) and columns (for sheet_metadata results)
231 sheet_json_schema
, columns
= get_sheet_schema_columns(sheet_metadata
)
233 return sheet_json_schema
, columns
236 def get_abs_path(path
):
237 return os
.path
.join(os
.path
.dirname(os
.path
.realpath(__file__
)), path
)
239 def get_schemas(client
, spreadsheet_id
):
243 for stream_name
, stream_metadata
in STREAMS
.items():
244 schema_path
= get_abs_path('schemas/{}.json'.format(stream_name
))
245 with open(schema_path
) as file:
246 schema
= json
.load(file)
247 schemas
[stream_name
] = schema
248 mdata
= metadata
.new()
251 # https://github.com/singer-io/getting-started/blob/master/docs/DISCOVERY_MODE.md#singer-python-helper-functions
253 # https://github.com/singer-io/singer-python/blob/master/singer/metadata.py#L25-L44
254 mdata
= metadata
.get_standard_metadata(
256 key_properties
=stream_metadata
.get('key_properties', None),
257 valid_replication_keys
=stream_metadata
.get('replication_keys', None),
258 replication_method
=stream_metadata
.get('replication_method', None)
260 field_metadata
[stream_name
] = mdata
262 if stream_name
== 'spreadsheet_metadata':
263 api
= stream_metadata
.get('api', 'sheets')
264 params
= stream_metadata
.get('params', {})
265 querystring
= '&'.join(['%s=%s' % (key
, value
) for (key
, value
) in params
.items()])
266 path
= '{}?{}'.format(stream_metadata
.get('path').replace('{spreadsheet_id}', \
267 spreadsheet_id
), querystring
)
269 # GET spreadsheet_metadata, which incl. sheets (basic metadata for each worksheet)
270 spreadsheet_md_results
= client
.get(path
=path
, params
=querystring
, api
=api
, \
271 endpoint
=stream_name
)
273 sheets
= spreadsheet_md_results
.get('sheets')
275 # Loop thru each worksheet in spreadsheet
277 # GET sheet_json_schema for each worksheet (from function above)
278 sheet_json_schema
, columns
= get_sheet_metadata(sheet
, spreadsheet_id
, client
)
279 # LOGGER.info('columns = {}'.format(columns))
281 sheet_title
= sheet
.get('properties', {}).get('title')
282 schemas
[sheet_title
] = sheet_json_schema
283 sheet_mdata
= metadata
.new()
284 sheet_mdata
= metadata
.get_standard_metadata(
285 schema
=sheet_json_schema
,
286 key_properties
=['__sdc_row'],
287 valid_replication_keys
=None,
288 replication_method
='FULL_TABLE'
290 field_metadata
[sheet_title
] = sheet_mdata
292 return schemas
, field_metadata