---
title: "Exporting and importing data between Google Sheets with Google Apps Script"
author: "Andrea Guerri"
date: 2025-01-06T09:28:09+00:00
modified: 2025-01-06T11:55:46+00:00
canonical_url: https://pulse.appsscript.info/p/2025/01/exporting-and-importing-data-between-google-sheets-with-google-apps-script/
markdown_url: https://pulse.appsscript.info/p/2025/01/exporting-and-importing-data-between-google-sheets-with-google-apps-script.md
categories:
  - "Beginner"
  - "Drive"
  - "Sheets"
  - "Solutions"
tags:
  - "Apps Script Community"
featured_image: https://pulse.appsscript.info/files/2025/01/Gemini_Generated_Image_sqst5dsqst5dsqst-e1736155652486.jpg
description: "Tired of hitting the dreaded \"File too large\" error when using IMPORTRANGE in Google Sheets? There's a better way! In this post by Andrea Guerri you will see show you how to use Google Apps Script to export and import data between your spreadsheets using CSV files. This method is more reliable, especially for large datasets, and offers greater flexibility. This approach bypasses IMPORTRANGE limitations, reduces connectivity issues, and allows for seamless automation. Ready to dive in? Continue reading to get the scripts and learn how to set up these solutions."
---

# Exporting and importing data between Google Sheets with Google Apps Script

![Exporting and importing data between Google Sheets with Google Apps Script](https://pulse.appsscript.info/files/2025/01/Gemini_Generated_Image_sqst5dsqst5dsqst-e1736155652486.jpg)

![](https://pulse.appsscript.info/files/2025/01/Gemini_Generated_Image_sqst5dsqst5dsqst-e1736155652486-1024x570.jpg)

When working with Google Sheets, you often use formulas like `IMPORTRANGE` to transfer data between files. However, `IMPORTRANGE` is not always reliable, especially when files become too large and you see the message “The file is too large.” In such cases, switching to a **Google Apps Script**-based solution that uses CSV files for data export and import is more efficient and flexible.

Here’s how you can create a system to export data from a Google Sheet to a CSV file and then import it into another sheet.

---

## **Export Data Script**

This script exports data from a sheet named `YOUR_TAB_NAME` to a CSV file saved in Google Drive.

```
function exportToCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";

const ss = SpreadsheetApp.getActive();
const sourceSheet = ss.getSheetByName(SheetName);

 // Get the values ​​as displayed in the sheet
 const dataValues = sourceSheet.getDataRange().getDisplayValues();

 // Creating CSV content
 const csvContent = dataValues.map(row => row.join(";")).join("\n");

 // Check if there is data
 if(csvContent == "") return;

 try {
 
 const fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
 
 DriveApp.getFileById(fileId).setContent(csvContent);
 
 } catch {

 DriveApp.createFile(NameFile + ".csv", csvContent, MimeType.CSV);

 }
}
```

### **How It Works**

1. **Fetches data** from the specified sheet (`YOUR_TAB_NAME`).
2. **Creates CSV content**, joining data with the `;` separator.
3. **Updates the CSV file** if it already exists or creates a new one.

---

## **Import Data Script**

This script imports data from a CSV file into a Google Sheet named `YOUR_TAB_NAME`.

```
function importFromCSV() {
 const SheetName = "YOUR_TAB_NAME";
 const NameFile = "YOUR_FILE_NAME";
 const Separator = ";"; // Change your separator if needed

 const destinationSheet = SpreadsheetApp.getActive().getSheetByName(SheetName);

 let fileId;

 try {
 // Search for the file and get its ID
 fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
 } catch {
 Logger.log("Il file '" + NameFile + ".csv' non è stato trovato in Google Drive.");
 return;
 }

 const file = DriveApp.getFileById(fileId);
 const property = PropertiesService.getDocumentProperties();

 // Check if the last imported data has already been loaded
 const lastModified = property.getProperty("lastModified");

 const timeStamp = file.getLastUpdated().toUTCString();
 property.setProperty("lastModified", timeStamp);

 if (lastModified == timeStamp) return;

 // Retrieve the CSV content
 const csvContent = file.getBlob().getDataAsString();

 // Split the content into rows and then into columns using the specified separator
 const data = csvContent
 .split("\n") // Split into rows
 .map(row => row.split(Separator)); // Split each row into columns using the specified separator

 destinationSheet.clearContents();
 destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
```

### **How It Works**

1. **Finds the CSV file** in Google Drive.
2. **Checks for changes** by comparing the file’s last modification time with a stored property value.
3. **Reads data** from the CSV file and imports it into the sheet, clearing existing data first.

---

## **Automation with Triggers**

To automate the import process, you can use a **time-based trigger** to run the script at regular intervals (e.g., every minute, hour, etc.).

### **Setting Up a Trigger**

1. Go to **Apps Script Editor** (Extensions > Apps Script).
2. Click on **Triggers (Clock icon)** or **Tools > Triggers** in the editor.
3. Create a new trigger:

- Choose the `importFromCSV` function.
- Select “Time-driven trigger.”
- Specify the frequency (e.g., every minute).

With this trigger, your script will regularly check for updates in the CSV file and automatically import new data.

---

### **Explanation of the Separator Usage**

Why the semicolon (;) is used: Descriptions or text fields may already use commas (,), and using them as a separator could lead to incorrect data splits. Using semicolons avoids this issue.

Alternative separator: If semicolons (;) are also present in the data, it’s recommended to use a unique symbol, such as §.

To update the separator, replace “;” with “§” in the following line:

```
<span style="color: #339966;"><span style="color: #008000;">.map(row => row.split("§"));</span> <span style="color: #999999;">// Update the separator here</span></span>
```

This makes the script adaptable to various data scenarios.

---

## **Why Use This Method?**

### **Advantages**

- **Avoid **`**IMPORTRANGE**`** Limits**: No errors related to overly large files.
- **Efficiency**: Data is transferred as CSV, reducing connectivity issues between files.
- **Automation**: Imports happen without manual intervention.

### **Limitations**

- **Maintenance**: Scripts need to be managed and updated manually if changes occur.
- **Security**: Ensure file and script access is secure.

---

With these scripts and a configured trigger, you can reliably and efficiently handle large volumes of data between Google Sheets. If you need further customizations or help setting it up, feel free to ask!

---

*Published by [Andrea Guerri](https://pulse.appsscript.info/p/author/andreaguerri/) on [Pulse](https://pulse.appsscript.info/). Original article: [https://pulse.appsscript.info/p/2025/01/exporting-and-importing-data-between-google-sheets-with-google-apps-script/](https://pulse.appsscript.info/p/2025/01/exporting-and-importing-data-between-google-sheets-with-google-apps-script/)*