Exporting and importing data between Google Sheets with Google Apps Script

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:

.map(row => row.split("§")); // Update the separator here

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!