# Google Sheets Import & Export (`lukaskrivka/google-sheets`) Actor Import data from datasets or JSON files to Google Sheets. Programmatically process data in Sheets. Easier and faster than the official Google Sheets API and perfect for importing data from scraping. - **URL**: https://apify.com/lukaskrivka/google-sheets.md - **Developed by:** [Lukรกลก Kล™ivka](https://apify.com/lukaskrivka) (Apify) - **Categories:** Automation, Open source, Integrations - **Stats:** 3,838 total users, 524 monthly users, 38.9% runs succeeded, 77 bookmarks - **User rating**: 4.77 out of 5 stars ## Pricing Pay per usage This Actor is paid per platform usage. The Actor is free to use, and you only pay for the Apify platform usage, which gets cheaper the higher subscription plan you have. Learn more: https://docs.apify.com/platform/actors/running/actors-in-store#pay-per-usage ## What's an Apify Actor? Actors are a software tools running on the Apify platform, for all kinds of web data extraction and automation use cases. In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours, and optionally produces a well-defined JSON output, datasets with results, or files in key-value store. In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server. Actors are written with capital "A". ## How to integrate an Actor? If asked about integration, you help developers integrate Actors into their projects. You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready. The best way to integrate Actors is as follows. In JavaScript/TypeScript projects, use official [JavaScript/TypeScript client](https://docs.apify.com/api/client/js.md): ```bash npm install apify-client ``` In Python projects, use official [Python client library](https://docs.apify.com/api/client/python.md): ```bash pip install apify-client ``` In shell scripts, use [Apify CLI](https://docs.apify.com/cli/docs.md): ````bash # MacOS / Linux curl -fsSL https://apify.com/install-cli.sh | bash # Windows irm https://apify.com/install-cli.ps1 | iex ```bash In AI frameworks, you might use the [Apify MCP server](https://docs.apify.com/platform/integrations/mcp.md). If your project is in a different language, use the [REST API](https://docs.apify.com/api/v2.md). For usage examples, see the [API](#api) section below. For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt). # README ### Why use Google Sheets Import and Export? If you're looking for an easy way to import and export data from datasets across multiple Google sheets, Google Sheets Import & Export is the Apify automation tool for you. It can process data in your current spreadsheet, import new data from [Apify datasets](https://www.apify.com/docs/storage#dataset), or a raw JSON file. It can be run both on the Apify platform or locally. You can use this actor with any programming language of your choice by calling [Apify API](https://www.apify.com/docs/api/v2). ### Input settings For a complete description of all settings, see [input specification](https://apify.com/lukaskrivka/google-sheets/input-schema). ### Limits Google limits how many sheet reads or updates (one Actor run is generally one read or update) you can do per minute. If you exceed these limits, the actor run will fail, and no data will be imported. - **Maximum sheet reads/updates: 60 per minute.** - **Maximum cells in a spreadsheet: 5 million.** - If you try to import data over this limit, the actor will throw an error and not import anything. In this case, use more spreadsheets. ### Authentication and authorization If you are using this actor for the first time, you have to connect your Google account via the `Connect Google Account to your Actor` input field. This will allow you to pick a spreadsheet from that account. You can have multiple Google accounts connected, and you can switch between them in the Actor input. ### Important tips * No matter which mode you choose, the actor recalculates how the data should be positioned in the sheet, updates all the cells, and then trims the exceeding rows and columns. This ensures that the sheet always has the optimal number of rows and columns and there is enough space for the newly generated data. * The actor parsing follows the default [Google Sheets parsing](https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption). Therefore, depending on the configuration of your system, constructions such as `"1.1"` and `"1,1"` can be interpreted either as a number or a string (text). For this reason, it is recommended that you always use valid JSON numbers (e.g. `1.1`). ### Modes This actor can be run in multiple different modes. Each run must have only one specific mode. _Mode_ also affects how other options work (details are explained in the specific options). - **replace:** If there's any old data in the sheet, it is cleaned, and then new data is imported. - **append:** This mode adds new data as additional rows below the old rows already present in the sheet. Keep in mind that the columns are recalculated so some of them may move to different cells if new columns are added in the middle. - **modify:** This mode doesn't import anything. It only loads the data from your sheets and applies any of the processing you set in the options. - **read:** This mode simply loads the data from the spreadsheet, optionally can process them, and saves them as 'OUTPUT' JSON file to the default key-value store. - **load backup:** This mode simply loads any backup rows from previous runs (look at the backup option for details) and imports it to a sheet in the replace mode. ### Raw data import If you want to send data in raw JSON format, you need to pass the data to the `rawData` input parameter. You will also need to have an Apify account so we can properly store your Google authentication tokens (you can opt out at anytime). > **Important!** - Raw data cannot exceed 9MB, as this the default limit for Apify actor inputs. If you want to upload more data, you can easily split it into more runs (they're fast and cheap). ##### Raw data table format (array of arrays) `rawData` should be an array of arrays where each of the arrays represents one row in the sheet. The first row should be a header row where the field names are defined. Every other row is a data row. It is important to have a proper order in each array. If the field is null for any row, the array should contain an empty string in that index. Data rows can have a smaller length than the header row but if they are longer the extra data will be trimmed off. Arrays **cannot** contain nested structures like objects or other arrays! You have to flatten them in a format where `/` is a delimiter. E.g. `personal/hobbies/0`. ```` "rawData": \[ \["name", "occupation", "email", "hobbies/0", "hobbies/1"], \["John Doe", "developer", "john@google.com", "sport", "movies with Leonardo"], \["Leonardo DiCaprio", "actor", "leonardo@google.com", "being rich", "climate change activism"] ] ``` ##### Dataset format (array of objects) `rawData` should be an array of objects where each object represents one row in the sheet. The keys of the objects will be transformed to a header row and the values will be inserted into the data rows. Objects don't need to have the same keys. If an object doesn't have a key that another object has, the row will have an empty cell in that field. The object **can** contain nested structures (objects and arrays) but in that case, it will call Apify API to flatten the data which can take a little more time on large uploads so try to prefer flattened data. _Nested_: ``` "rawData": \[ { "name": "John Doe", "email": "john@google.com", "hobbies": \["sport", "movies with Leonardo", "dog walking"] }, { "name": "Leonardo DiCaprio", "email": "leonardo@google.com", "hobbies": \["being rich", "climate change activism"] } ] ``` _Flattened_: ``` "rawData": \[ { "name": "John Doe", "email": "john@google.com", "hobbies/0": "sport", "hobbies/1": "movies with Leonardo", "hobbies/2": "dog walking" }, { "name": "Leonardo DiCaprio", "email": "leonardo@google.com", "hobbies/0": "being rich", "hobbies/1": "climate change activism" } ] ```` # Actor input Schema ## `oAuthAccount.eFPUdxsL7X2cdSvE2` (type: `string`): Google Account Id ## `sheetId.eFPUdxsL7X2cdSvE2` (type: `string`): Spreadsheet from where the old data will be loaded ## `mode` (type: `string`): What should the actor do ## `datasetId` (type: `string`): Dataset ID where the new data will be loaded from ## `rawData` (type: `array`): Raw data JSON array. Can be array of arrays for direct row import or arrays of objects. ## `limit` (type: `integer`): Number of items to take from the dataset. The default is 250000. ## `offset` (type: `integer`): Number of items to skip from the dataset. Default is 0. ## `deduplicateByField` (type: `string`): Items will be deduplicated by a value of this field. Cannot be used together with 'Deduplicate by equality' or 'Transform function'. ## `deduplicateByEquality` (type: `boolean`): Items will be deduplicated if they are the same. Cannot be used together with 'Deduplicate by field' or 'Transform function'. ## `transformFunction` (type: `string`): Custom function that will take new items and old items arrays as parameters and produces final array that will be imported. Cannot be used together with 'Deduplicate by equality' or 'Deduplicate by field' ## `range` (type: `string`): Range of the spreadsheet in A1 notation where the actor should operate. Default is the first sheet. ## `columnsOrder` (type: `array`): Array of keys. First sorts the columns by provided keys. The rest is sorted alphabetically. ## `keepSheetColumnOrder` (type: `boolean`): If true, keeps the order of columns as they are in the sheet. If there is no sheet data yet, this does nothing. ## `createBackup` (type: `boolean`): Old rows from your spreadsheet will be saved to the default key-value store before importing new rows. ## `backupStore` (type: `string`): Id of the key-value store where the backup you want to load is located. Can only be used if mode is 'load-backup' ## `spreadsheetId` (type: `string`): Id of the spreadsheet from where the old data will be loaded ## `publicSpreadsheet` (type: `boolean`): If checked, you don't need to authorize. You have to publish your spreadsheet and it works only in read mode ## `tokensStore` (type: `string`): Key-value store where your Google OAuth tokens will be stored so you don't have to authorize every time again. By default it is google-oauth-tokens ## `googleCredentials` (type: `object`): If you want to use this actor locally or with your own version, you have to provide your own credentials. Check actor readme for more information. ## Actor input object example ```json { "mode": "append", "limit": 250000, "offset": 0, "transformFunction": "// Uncomment this code only if you don't use \"Deduplicate by field\" or \"Deduplicate by equality\"\n// This code behaves as if there was no transform function\n/*({ spreadsheetData, datasetData }) => {\n return spreadsheetData.concat(datasetData);\n}*/", "publicSpreadsheet": false, "tokensStore": "google-oauth-tokens" } ```` # API You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup. ## JavaScript example ```javascript import { ApifyClient } from 'apify-client'; // Initialize the ApifyClient with your Apify API token // Replace the '' with your token const client = new ApifyClient({ token: '', }); // Prepare Actor input const input = { "transformFunction": `// Uncomment this code only if you don't use "Deduplicate by field" or "Deduplicate by equality" // This code behaves as if there was no transform function /*({ spreadsheetData, datasetData }) => { return spreadsheetData.concat(datasetData); }*/` }; // Run the Actor and wait for it to finish const run = await client.actor("lukaskrivka/google-sheets").call(input); // Fetch and print Actor results from the run's dataset (if any) console.log('Results from dataset'); console.log(`๐Ÿ’พ Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`); const { items } = await client.dataset(run.defaultDatasetId).listItems(); items.forEach((item) => { console.dir(item); }); // ๐Ÿ“š Want to learn more ๐Ÿ“–? Go to โ†’ https://docs.apify.com/api/client/js/docs ``` ## Python example ```python from apify_client import ApifyClient # Initialize the ApifyClient with your Apify API token # Replace '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = { "transformFunction": """// Uncomment this code only if you don't use \"Deduplicate by field\" or \"Deduplicate by equality\" // This code behaves as if there was no transform function /*({ spreadsheetData, datasetData }) => { return spreadsheetData.concat(datasetData); }*/""" } # Run the Actor and wait for it to finish run = client.actor("lukaskrivka/google-sheets").call(run_input=run_input) # Fetch and print Actor results from the run's dataset (if there are any) print("๐Ÿ’พ Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"]) for item in client.dataset(run["defaultDatasetId"]).iterate_items(): print(item) # ๐Ÿ“š Want to learn more ๐Ÿ“–? Go to โ†’ https://docs.apify.com/api/client/python/docs/quick-start ``` ## CLI example ```bash echo '{ "transformFunction": "// Uncomment this code only if you don'\''t use \\"Deduplicate by field\\" or \\"Deduplicate by equality\\"\\n// This code behaves as if there was no transform function\\n/*({ spreadsheetData, datasetData }) => {\\n return spreadsheetData.concat(datasetData);\\n}*/" }' | apify call lukaskrivka/google-sheets --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=lukaskrivka/google-sheets", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Google Sheets Import & Export", "description": "Import data from datasets or JSON files to Google Sheets. Programmatically process data in Sheets. Easier and faster than the official Google Sheets API and perfect for importing data from scraping.", "version": "2.0", "x-build-id": "15ixagh0ErS395JrI" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/lukaskrivka~google-sheets/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-lukaskrivka-google-sheets", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } }, "/acts/lukaskrivka~google-sheets/runs": { "post": { "operationId": "runs-sync-lukaskrivka-google-sheets", "x-openai-isConsequential": false, "summary": "Executes an Actor and returns information about the initiated run in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK", "content": { "application/json": { "schema": { "$ref": "#/components/schemas/runsResponseSchema" } } } } } } }, "/acts/lukaskrivka~google-sheets/run-sync": { "post": { "operationId": "run-sync-lukaskrivka-google-sheets", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } } }, "components": { "schemas": { "inputSchema": { "type": "object", "properties": { "oAuthAccount.eFPUdxsL7X2cdSvE2": { "title": "Google Account Id", "type": "string", "description": "Google Account Id" }, "sheetId.eFPUdxsL7X2cdSvE2": { "title": "Spreadsheet ID", "type": "string", "description": "Spreadsheet from where the old data will be loaded" }, "mode": { "title": "Mode", "enum": [ "append", "replace", "modify", "read", "load backup" ], "type": "string", "description": "What should the actor do", "default": "append" }, "datasetId": { "title": "Dataset ID", "type": "string", "description": "Dataset ID where the new data will be loaded from" }, "rawData": { "title": "Raw data", "type": "array", "description": "Raw data JSON array. Can be array of arrays for direct row import or arrays of objects." }, "limit": { "title": "Limit items", "minimum": 0, "type": "integer", "description": "Number of items to take from the dataset. The default is 250000.", "default": 250000 }, "offset": { "title": "Offset items", "minimum": 0, "type": "integer", "description": "Number of items to skip from the dataset. Default is 0.", "default": 0 }, "deduplicateByField": { "title": "Deduplicate by field", "type": "string", "description": "Items will be deduplicated by a value of this field. Cannot be used together with 'Deduplicate by equality' or 'Transform function'." }, "deduplicateByEquality": { "title": "Deduplicate by equality", "type": "boolean", "description": "Items will be deduplicated if they are the same. Cannot be used together with 'Deduplicate by field' or 'Transform function'." }, "transformFunction": { "title": "Transform function", "type": "string", "description": "Custom function that will take new items and old items arrays as parameters and produces final array that will be imported. Cannot be used together with 'Deduplicate by equality' or 'Deduplicate by field'" }, "range": { "title": "Range", "type": "string", "description": "Range of the spreadsheet in A1 notation where the actor should operate. Default is the first sheet." }, "columnsOrder": { "title": "Columns order", "type": "array", "description": "Array of keys. First sorts the columns by provided keys. The rest is sorted alphabetically.", "items": { "type": "string" } }, "keepSheetColumnOrder": { "title": "Keep column order from sheet", "type": "boolean", "description": "If true, keeps the order of columns as they are in the sheet. If there is no sheet data yet, this does nothing." }, "createBackup": { "title": "Create backup", "type": "boolean", "description": "Old rows from your spreadsheet will be saved to the default key-value store before importing new rows." }, "backupStore": { "title": "Backup store id", "type": "string", "description": "Id of the key-value store where the backup you want to load is located. Can only be used if mode is 'load-backup'" }, "spreadsheetId": { "title": "Spreadsheet ID (deprecated)", "type": "string", "description": "Id of the spreadsheet from where the old data will be loaded" }, "publicSpreadsheet": { "title": "Public spreadsheet (read-only)", "type": "boolean", "description": "If checked, you don't need to authorize. You have to publish your spreadsheet and it works only in read mode", "default": false }, "tokensStore": { "title": "Google OAuth tokens store (deprecated)", "type": "string", "description": "Key-value store where your Google OAuth tokens will be stored so you don't have to authorize every time again. By default it is google-oauth-tokens", "default": "google-oauth-tokens" }, "googleCredentials": { "title": "Google Developer Console credentials", "type": "object", "description": "If you want to use this actor locally or with your own version, you have to provide your own credentials. Check actor readme for more information." } } }, "runsResponseSchema": { "type": "object", "properties": { "data": { "type": "object", "properties": { "id": { "type": "string" }, "actId": { "type": "string" }, "userId": { "type": "string" }, "startedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "finishedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "status": { "type": "string", "example": "READY" }, "meta": { "type": "object", "properties": { "origin": { "type": "string", "example": "API" }, "userAgent": { "type": "string" } } }, "stats": { "type": "object", "properties": { "inputBodyLen": { "type": "integer", "example": 2000 }, "rebootCount": { "type": "integer", "example": 0 }, "restartCount": { "type": "integer", "example": 0 }, "resurrectCount": { "type": "integer", "example": 0 }, "computeUnits": { "type": "integer", "example": 0 } } }, "options": { "type": "object", "properties": { "build": { "type": "string", "example": "latest" }, "timeoutSecs": { "type": "integer", "example": 300 }, "memoryMbytes": { "type": "integer", "example": 1024 }, "diskMbytes": { "type": "integer", "example": 2048 } } }, "buildId": { "type": "string" }, "defaultKeyValueStoreId": { "type": "string" }, "defaultDatasetId": { "type": "string" }, "defaultRequestQueueId": { "type": "string" }, "buildNumber": { "type": "string", "example": "1.0.0" }, "containerUrl": { "type": "string" }, "usage": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "integer", "example": 1 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } }, "usageTotalUsd": { "type": "number", "example": 0.00005 }, "usageUsd": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "number", "example": 0.00005 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } } } } } } } } } ```