Exporting to TSV using Google Apps Script

Oct 13, 2025 by leolavanderia.com.br 44 views

Introduction

Google Sheets is a powerful and versatile spreadsheet tool that allows users to create, edit, and share spreadsheets online. One of the key features of Google Sheets is its ability to integrate with other Google apps and services, such as Google Drive and Google Apps Script. In this article, we will explore how to export a Google Sheet to a Tab Separated Values (TSV) file using Google Apps Script.

What is a TSV File?

A TSV file is a type of text file that contains data separated by tabs. It is similar to a CSV (Comma Separated Values) file, but instead of using commas to separate data, TSV files use tabs. TSV files are often used for data exchange between different applications and systems.

Why Export to TSV?

There are several reasons why you might want to export a Google Sheet to a TSV file:

Data exchange: TSV files can be easily imported into other applications and systems, making it a convenient format for data exchange.
Data analysis: TSV files can be easily analyzed using tools like Excel, Google Data Studio, or other data analysis software.
Data sharing: TSV files can be easily shared with others, either by uploading them to a cloud storage service or by sending them via email.

Exporting to TSV using Google Apps Script

Google Apps Script is a powerful tool that allows you to automate tasks and create custom functions in Google Sheets. To export a Google Sheet to a TSV file using Google Apps Script, you will need to follow these steps:

Step 1: Create a New Script

To create a new script, follow these steps:

Open your Google Sheet.
Click on the "Tools" menu.
Select "Script editor".
This will open the Google Apps Script editor.

Step 2: Write the Script

To write the script, follow these steps:

In the script editor, click on the "Blank project" button.
Delete any existing code in the editor.
Paste the following code into the editor:

function exportToTSV() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var tsv = "";
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      tsv += data[i][j] + "\t";
    }
    tsv += "\n";
  }
  var blob = Utilities.newBlob(tsv, "text/tab-separated-values");
  var file = DriveApp.createFile(blob);
  file.setName("export.tsv");
}

This script will export the current sheet to a TSV file named "export.tsv".

Step 3: Run the Script

To run the script, follow these steps:

Save the script by clicking on the "File" menu and selecting "Save".
Click on the "Run" button in the script editor.
The script will export the current sheet to a TSV file named "export.tsv".

Tips and Variations

Here are some tips and variations to keep in mind when exporting to TSV using Google Apps Script:

Exporting a specific range: To export a specific range of cells, you can modify the script to use the getRange() method instead of getDataRange().
Exporting to a different file format: To export to a different file format, such as CSV or Excel, you can modify the script to use the Utilities.newBlob() method with a different file type.
Exporting to a different location: To export to a different location, such as a different folder or drive, you can modify the script to use the DriveApp.createFile() method with a different file location.

Conclusion

Introduction

In our previous article, we explored how to export a Google Sheet to a Tab Separated Values (TSV) file using Google Apps Script. In this article, we will answer some frequently asked questions about exporting to TSV using Google Apps Script.

Q: What is the difference between a TSV file and a CSV file?

A: A TSV file and a CSV file are both types of text files that contain data separated by a delimiter. The main difference between the two is the delimiter used to separate the data. A TSV file uses tabs to separate the data, while a CSV file uses commas.

Q: Why would I want to export to TSV instead of CSV?

A: There are several reasons why you might want to export to TSV instead of CSV:

Data exchange: TSV files can be easily imported into other applications and systems, making it a convenient format for data exchange.
Data analysis: TSV files can be easily analyzed using tools like Excel, Google Data Studio, or other data analysis software.
Data sharing: TSV files can be easily shared with others, either by uploading them to a cloud storage service or by sending them via email.

Q: How do I export a specific range of cells to a TSV file?

A: To export a specific range of cells to a TSV file, you can modify the script to use the getRange() method instead of getDataRange(). For example:

function exportToTSV() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:B10");
  var data = range.getValues();
  var tsv = "";
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      tsv += data[i][j] + "\t";
    }
    tsv += "\n";
  }
  var blob = Utilities.newBlob(tsv, "text/tab-separated-values");
  var file = DriveApp.createFile(blob);
  file.setName("export.tsv");
}

This script will export the range A1:B10 to a TSV file named "export.tsv".

Q: How do I export to a different file format, such as CSV or Excel?

A: To export to a different file format, such as CSV or Excel, you can modify the script to use the Utilities.newBlob() method with a different file type. For example:

function exportToCSV() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var csv = "";
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      csv += data[i][j] + ",";
    }
    csv += "\n";
  }
  var blob = Utilities.newBlob(csv, "text/csv");
  var file = DriveApp.createFile(blob);
  file.setName("export.csv");
}

This script will export the current sheet to a CSV file named "export.csv".

Q: How do I export to a different location, such as a different folder or drive?

A: To export to a different location, such as a different folder or drive, you can modify the script to use the DriveApp.createFile() method with a different file location. For example:

function exportToDrive() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var tsv = "";
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      tsv += data[i][j] + "\t";
    }
    tsv += "\n";
  }
  var blob = Utilities.newBlob(tsv, "text/tab-separated-values");
  var folder = DriveApp.getFolderById("folder_id");
  var file = folder.createFile(blob);
  file.setName("export.tsv");
}

This script will export the current sheet to a TSV file named "export.tsv" in the folder with the ID "folder_id".

Conclusion

In this article, we answered some frequently asked questions about exporting to TSV using Google Apps Script. We covered topics such as the difference between TSV and CSV files, how to export a specific range of cells, how to export to a different file format, and how to export to a different location. With this knowledge, you should be able to easily export your Google Sheets to TSV files using Google Apps Script.