Implementation:Mage ai Mage ai Google Sheets Transform

Knowledge Sources	Mage Integrations Google Sheets API
Domains	Data_Integration, Google_Sheets, Transform
Last Updated	2026-02-09 00:00 GMT

Overview

Comprehensive data transformation functions for the Mage Google Sheets source connector, handling metadata transformation, Excel serial number date conversion, and type-safe cell value transformations for all Google Sheets data types.

Description

This module provides a set of transformation functions used to convert raw Google Sheets API response data into structured, typed records suitable for Singer output. It handles:

• Metadata transformation - transform_sheet_metadata, transform_spreadsheet_metadata, and transform_file_metadata reshape Google Sheets API metadata into flat record structures, adding spreadsheet IDs and sheet URLs.
• Date/time conversion - excel_to_dttm_str converts Excel Date Serial Numbers to ISO datetime strings, handling timezone conversion via pytz. Separate functions handle DATE_TIME, DATE, and TIME column types.
• Type-safe value transformation - get_column_value dispatches to type-specific handlers based on column type: numberType.DATE_TIME, numberType.DATE, numberType.TIME, numberType (integer/float), stringValue, and boolValue.
• Sheet data transformation - transform_sheet_data iterates through sheet rows, mapping column values by index, injecting __google_spreadsheet_id, __google_sheet_id, and __google_sheet_row metadata, and applying column-type-specific transformations.
• Number handling - transform_sheet_number_data and transform_sheet_decimal_data handle US number format (comma removal), scientific notation, float rounding to 15 decimal places, and integer preservation.

Usage

Called during the Google Sheets sync process to transform raw spreadsheet data and metadata before Singer record emission.

Code Reference

Source Location

• Repository: mage-ai
• File: mage_integrations/mage_integrations/sources/google_sheets/transform.py
• Lines: 1-298

Signature

def transform_sheet_metadata(spreadsheet_id, sheet, columns):
def transform_spreadsheet_metadata(spreadsheet_metadata):
def transform_file_metadata(file_metadata):
def excel_to_dttm_str(string_value, excel_date_sn, timezone_str=None):
def transform_sheet_data(spreadsheet_id, sheet_id, sheet_title, from_row, columns, sheet_data_rows, unformatted_rows):
def get_column_value(value, unformatted_value, sheet_title, col_name, col_letter, row_num, col_type, row):

Import

from mage_integrations.sources.google_sheets.transform import (
    transform_sheet_metadata,
    transform_spreadsheet_metadata,
    transform_file_metadata,
    transform_sheet_data,
)

I/O Contract

Key Functions

Function	Inputs	Output	Description
transform_sheet_metadata	spreadsheet_id, sheet, columns	dict	Enriches sheet properties with spreadsheetId, sheetUrl, and columns
transform_spreadsheet_metadata	spreadsheet_metadata (dict)	list[dict]	Removes defaultFormat/sheets, wraps in single-element array
transform_file_metadata	file_metadata (dict)	list[dict]	Removes photo/permission fields from lastModifyingUser, wraps in array
excel_to_dttm_str	string_value, excel_date_sn, timezone_str	(str, bool)	Converts Excel serial number to UTC datetime string; returns (value, is_error)
transform_sheet_data	spreadsheet_id, sheet_id, sheet_title, from_row, columns, sheet_data_rows, unformatted_rows	(list[dict], int)	Transforms all rows with type-safe value conversion; returns (records, next_row_num)
get_column_value	value, unformatted_value, sheet_title, col_name, col_letter, row_num, col_type, row	varies	Type-dispatched cell value transformation

Supported Column Types

Column Type	Handler	Output Type
numberType.DATE_TIME	transform_sheet_datetime_data	ISO datetime string
numberType.DATE	transform_sheet_date_data	ISO date string (YYYY-MM-DD)
numberType.TIME	transform_sheet_time_data	HH:MM:SS string
numberType	transform_sheet_number_data	int or float (rounded to 15 decimal places)
stringValue	Direct cast	str
boolValue	transform_sheet_boolean_data	bool (with string/int/float coercion)

Usage Examples

from mage_integrations.sources.google_sheets.transform import transform_sheet_data

records, next_row = transform_sheet_data(
    spreadsheet_id='abc123',
    sheet_id=0,
    sheet_title='Sheet1',
    from_row=2,
    columns=column_metadata,
    sheet_data_rows=formatted_values,
    unformatted_rows=unformatted_values,
)

Related Pages

Implements Principle

• Principle:Mage_ai_Mage_ai_Singer_Message_Emission

Requires Environment

• Environment:Mage_ai_Mage_ai_Python_3_9_Runtime