Google Sheets – Read .tsv Files from Local Drive Using Apps Script

google sheetsgoogle-apps-script

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.
enter image description here
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")

Best Answer

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

Related Topic