Insert Data

Insert data into Google Spreadsheets from multiple sources including raw arrays, CSV text, CSV files, and Excel files. Supports three operation modes: add rows, replace data, and create new sheets.

Overview

The Insert Data tool is a comprehensive solution for getting data into Google Sheets. It supports:

• Multiple input formats: Raw 2D arrays, CSV text, CSV files, Excel files
• Three operation modes: Add rows (append/insert), replace data (with clear options), create new sheets
• Robust validation: Size limits, spreadsheet verification, sheet existence checks
• Safe defaults: Prevents accidental data loss with configurable clear behavior

Prerequisites

• A connected Google Account with OAuth authentication
• Google Sheets API enabled in your Google Cloud project
• Setup Guide

Parameters

Core Parameters

Parameter	Type	Required	Description
spreadsheet_id	string	Yes	Google Spreadsheet ID from URL
operation_mode	string	Yes	Operation mode: add_rows, replace_data, or new_sheet
sheet_name	string	Yes*	Target sheet name (*required for add_rows/replace_data, new sheet name for new_sheet)

Data Input Parameters (Mutually Exclusive)

Provide exactly one of these:

Parameter	Type	Description
data	array[array]	Raw 2D array (e.g., [["Name","Age"],["Alice",30]])
csv_data	string	CSV formatted text
csv_file_content	string	Base64-encoded CSV file
excel_file_content	string	Base64-encoded Excel file (.xlsx/.xls)

Mode-Specific Parameters

Parameter	Type	Default	Description
add_mode	string	append	For add_rows: append (end of data) or insert_at_range
replace_mode	string	clear_range	For replace_data: clear_sheet, clear_range, or overwrite
range	string	-	A1 notation range (without sheet prefix)
excel_sheet_name	string	-	For Excel with multiple sheets, which sheet to read

Additional Parameters

Parameter	Type	Default	Description
value_input_option	string	USER_ENTERED	RAW or USER_ENTERED (parse formulas/dates)
include_headers	boolean	true	Whether first row contains headers

Data Size Limits

To ensure fast performance and prevent abuse:

• Maximum rows: 5,000
• Maximum cells: 50,000
• Maximum file size: 25 MB

Tip: For larger datasets, split into multiple operations.

Usage Examples

Example 1: Add Rows (Append)

Add data to the end of existing data in a sheet.

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "add_rows",
    "sheet_name": "Customers",
    "data": [
      ["Alice Johnson", "alice@example.com", "2025-01-10"],
      ["Bob Smith", "bob@example.com", "2025-01-10"]
    ],
    "add_mode": "append"
  }
}

Example 2: Replace Data (Clear Range)

Replace existing data in a specific range.

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "replace_data",
    "sheet_name": "Dashboard",
    "range": "A1:E10",
    "replace_mode": "clear_range",
    "data": [
      ["Metric", "Q1", "Q2", "Q3", "Q4"],
      ["Revenue", 100000, 120000, 150000, 180000],
      ["Customers", 500, 650, 800, 1000]
    ]
  }
}

Example 3: Create New Sheet

Create a new sheet and populate it with data.

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "new_sheet",
    "sheet_name": "January 2025",
    "data": [
      ["Date", "Sales", "Notes"],
      ["2025-01-01", 5000, "New Year"],
      ["2025-01-02", 7500, "Strong start"]
    ]
  }
}

Example 4: Import CSV Text

Insert data from CSV text.

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "add_rows",
    "sheet_name": "Products",
    "csv_data": "Product,Price,Stock\nWidget,29.99,100\nGadget,49.99,50",
    "add_mode": "append"
  }
}

Example 5: Upload CSV File

For UI users, upload a CSV file which will be automatically encoded.

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "replace_data",
    "sheet_name": "Inventory",
    "csv_file_content": "<base64-encoded-file-content>",
    "replace_mode": "clear_sheet"
  }
}

Example 6: Upload Excel File

Import data from an Excel file (specific sheet).

{
  "tool": "google_sheets_Insert_Data",
  "arguments": {
    "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
    "operation_mode": "new_sheet",
    "sheet_name": "Imported Data",
    "excel_file_content": "<base64-encoded-file-content>",
    "excel_sheet_name": "Q4 Results"
  }
}

Operation Modes Explained

Add Rows Mode

Purpose: Add new data without removing existing data

Sub-modes:

• append: Automatically finds the next empty row and adds data there
• insert_at_range: Insert data starting at a specific cell (requires range parameter)

Use cases:

• Logging new records
• Adding form submissions
• Appending time-series data

Example flow:

1. Tool reads existing data to find last filled row
2. Inserts new data starting at next empty row
3. Existing data remains untouched

Replace Data Mode

Purpose: Replace existing data with new data

Sub-modes:

• clear_sheet: Clears entire sheet, writes starting at A1 (most aggressive)
• clear_range: Clears only target range, writes new data there (balanced, default)
• overwrite: Writes data without clearing (safest, data outside range remains)

Use cases:

• Refreshing dashboard data
• Updating entire datasets
• Replacing outdated information

Example flow (clear_range):

1. Tool clears cells in specified range
2. Writes new data to same range
3. Data outside range is preserved

New Sheet Mode

Purpose: Create a new sheet/tab and populate it

Behavior:

• Creates new sheet with specified name
• Writes data starting at A1
• Fails if sheet name already exists

Use cases:

• Monthly/weekly report generation
• Creating per-project sheets
• Organizing data into categories

Example flow:

1. Tool checks if sheet name exists
2. Creates new sheet with appropriate size
3. Writes data to A1

Replace Mode Decision Guide

Mode	Clears?	Best For	Risk Level
overwrite	No	Partial updates, preserving surrounding data	Low (safest)
clear_range	Only target range	Refreshing specific data sections	Medium (balanced)
clear_sheet	Entire sheet	Complete data replacement	High (aggressive)

Default: clear_range - provides a good balance of safety and clean replacement.

File Upload Guide (UI Users)

CSV Files

1. Navigate to your custom tool in "My Tools"
2. Scroll to Input Parameters section
3. Find the csv_file_content parameter
4. Click "Upload CSV File" button
5. Select your .csv file (max 25 MB)
6. File is automatically validated and encoded
7. Save the tool configuration

Excel Files

1. Same process as CSV
2. Find the excel_file_content parameter
3. Upload .xlsx or .xls file
4. Optionally specify excel_sheet_name if file has multiple sheets
5. First sheet is used by default if not specified

File Validation

Client-side validation checks:

• File size (max 25 MB)
• File type (.csv, .xlsx, .xls only)

Server-side validation checks:

• Decoded file size
• Parse-ability
• Row/cell limits (5,000 rows / 50,000 cells)

Response Format

Success Response

{
  "success": true,
  "message": "Data inserted successfully",
  "spreadsheet_id": "1abcDEFgHIjklMNOpQrStUvWxYz",
  "spreadsheet_url": "https://docs.google.com/spreadsheets/d/1abcDEFgHIjklMNOpQrStUvWxYz/edit",
  "sheet_name": "Data",
  "operation": "add_rows",
  "source": "CSV file",
  "rows_written": 100,
  "columns_written": 5,
  "cells_written": 500,
  "updated_range": "Data!A1:E100",
  "updated_rows": 100,
  "updated_columns": 5,
  "updated_cells": 500,
  "add_mode": "append"
}

Error Response

{
  "error": "Data has 10,000 rows, which exceeds the limit of 5,000 rows. Please split your data into smaller batches."
}

Common Use Cases

1. Daily Data Imports

Import CSV exports from other systems daily.

Workflow:

• Export CSV from source system
• Use add_rows with append mode
• Data accumulates over time
• Sort/filter in Google Sheets

2. Dashboard Refresh

Replace dashboard data with latest metrics.

Workflow:

• Generate current metrics as 2D array
• Use replace_data with clear_range mode
• Specify dashboard range (e.g., A1:F20)
• Dashboard formulas reference this data

3. Monthly Report Generation

Create new sheet for each month's report.

Workflow:

• Use new_sheet with name like "Jan 2025"
• Populate with month's data
• Previous months remain as historical record
• Easy month-over-month comparison

4. Excel Migration

Migrate data from Excel to Google Sheets.

Workflow:

• Upload Excel file
• Specify sheet name if multiple sheets
• Use new_sheet to create clean import
• Verify data, then integrate with other sheets

5. Form Data Collection

Collect form submissions into a sheet.

Workflow:

• Form submits data as 2D array
• Use add_rows with append
• Each submission adds a new row
• Automatic timestamp in first column

Troubleshooting

Size Limit Errors

Error: "Data has X rows, which exceeds the limit of 5,000 rows"

Solution:

• Split data into multiple batches
• Process in chunks (e.g., 4,000 rows at a time)
• Use pagination if importing from API

File Upload Errors

Error: "File size is X MB, which exceeds the limit of 25 MB"

Solution:

• Compress data (remove unnecessary columns)
• Split into multiple files
• For Excel, export as CSV (usually smaller)

Sheet Not Found

Error: "Sheet 'Data' does not exist in this spreadsheet"

Solution:

• Check sheet name spelling (case-sensitive)
• Use new_sheet mode to create it first
• Use List Sheets tool to see available sheets

Parsing Errors

Error: "Failed to parse CSV: ..."

Solution:

• Verify CSV format (commas, quotes properly escaped)
• Check for encoding issues (use UTF-8)
• Try opening in text editor to inspect

Authentication Errors

Error: "Google Sheets API is not enabled"

Solution:

• Enable Sheets API in Google Cloud Console
• See setup guide
• Verify OAuth scopes include Sheets access

Best Practices

1. Start Small

When importing large datasets:

• Test with a small sample first
• Verify column mapping and formatting
• Then import full dataset

2. Use Headers

Always include header row:

• Set include_headers: true
• Makes data self-documenting
• Easier to verify correct import

3. Choose Appropriate Mode

• Add rows: For cumulative data (logs, transactions)
• Replace data: For refreshable data (dashboards, reports)
• New sheet: For versioned data (monthly reports)

4. Use Clear Range

For replace operations:

• clear_range is safest default
• Preserves data outside target area
• Prevents accidental deletions

5. Validate Data First

Before importing:

• Check data structure (consistent columns)
• Verify no missing required fields
• Remove or handle null values

Credits Cost

2 credits per operation (regardless of data size within limits)

Related Tools

• Create Spreadsheet - Create new spreadsheets with initial data
• Google Sheets Get Values (coming soon) - Read data from sheets
• Google Sheets Update Values (coming soon) - Update specific cells/ranges

Support

Need help? Check our troubleshooting guide or contact support.