How to read * .tsv files from a local drive into a spreadsheet file using Apps Script?

Question

I have a *.tsv file in local storage on my computer. I want the file to be read from the apps script then the contents of the file are copied into a spreadsheet file. I do not want the file to be uploaded first. This concept is like copy-paste. The script that I have created is like this:

function importTSVFromLocal() {

  // Provide the full URL of the CSV file.
  var tsvUrl = "file:///C:/Users/{username}/Downloads/Test1%20-%20Sheet1.tsv";
  var tsvContent = UrlFetchApp.fetch(tsvUrl).getContentText();
  var tsvData = Utilities.parseTsv(tsvContent);

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, tsvData.length, tsvData[0].length).setValues(tsvData);

}

But it looks like the script that I created doesn't work. I got an error message :

Bad host name: file:///C:/Users/{username}/Downloads/Test1%20-%20Sheet1.tsv (line 5, file "Code")

Answer 1

The UrlFetchApp.fetch method doesn't work for your local files.

One other thing you need to change is the Utilities method. The method to use is actually Utilities.parseCsv, you need to specify the tab separator for tsv:

var tsvData = Utilities.parseCsv(tsvContent, '\t');

References:

https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app.html#fetch(String)

https://developers.google.com/apps-script/reference/utilities/utilities#parsecsvcsv,-delimiter

Answer 2

It's not possible to read a file from a local drive using Google Apps Script as it doesn't have any method to access local resources.

You could use Google Drive Backup and Sync to automatically upload files to Google Drive and once the files are in Google Drive they could be get by Google Apps Script by using the Drive Service.

Another alternative is to use Google Picker but this requires to use client-side code.

References

• https://developers.google.com/apps-script/