Import data from CSV file in Google Drive, to Google Sheet

Question

I am using SAS to generate two CSV files every 24 hours. And I have used a bat script to save the generated CSV files in a folder in Google Drive. The CSV files are replaced, so there will always only be these two files in the folder.

The CSV files are seperated by "," and contain only three columns or four columns.

I want to create a direct link between a google sheet and the CSV files, so that the google sheet updates to the newest numbers automatically.

I have tried using the function "ImportData" with no luck

=IMPORTDATA("https://drive.google.com/file/d/123231jshu231731/edit?usp=sharing")

where 123231jshu231731 is the file_id.

But the error I get is

Result was not automatically expanded, please insert more columns (896).

Which does not make sense as the file only have 3 columns

Hope some have a better solution to my automatization problem

Thanks

Answer 1

Publishing the csv files to the web

Apparently IMPORTDATA only works for documents published to the web. Two ways you could get it to work for you would be to:

1. Store your csv files in a public google drive folder
2. Host your csv files on the web some other way - I suppose you would need to run a sever on your machine with the csv files.

However, this involves making your csv data public to anyone connected to the internet (probably something you don't want)

---

Keeping the csv files private

If you want your data to remain private, you could make a script that periodically loads your csv data into the spreadsheet.

Google provides a service call Google Apps Script (GAS) which would allow you to write scripts in JavaScript to preform more complex tasks in google drive.

Within GAS, you can create something called an installable trigger which you can set to run periodically on a set time interval.

Here's a basic layout of how I'd go about writing the script to upload your csv data:

function timedTrigger() {
  // list the csv file ids
  var baseballScoresFileId = "123231jshu231731";
  var donutPricesFileId = "984732ageyn555646";

  // get the spreadsheet by it's id
  var ss = SpreadsheetApp.openById("the spreadsheet id");

  // get the sheets you want to print the data to
  var baseballSheet = ss.getSheetByName("Baseball Scores");
  var donutsSheet = ss.getSheetByName("Donuts Scores");

  // print the data to the first row first columnscript
  printCsvData(baseballScoresFileId, baseballSheet, 1, 1);

  printCsvData(donutPricesFileId, donutsSheet, 5, 2); // prints to B5
}

// This function loads the data from a csv fileId
//   and prints it to the sheet starting at the cell
//   located at (row, col)
// It works just like IMPORTDATA except you specify the row and col in the 
function printCsvData(fileId, sheet, row, col) {
  // get data from file
  var data = DriveApp.getFileById(fileId).getBlob().getDataAsString();

  // parse the csv string into two dimensional array
  var values = parseCsvData(data);

  // print the values into the range starting at row, col
  sheet.getRange(row, col, values.length, values[0].length).setValues(values);
}

// This function converts a string of comma
//   separated values and converts them into
//   a two dimensional array
function parseCsv(string) {
  var values = [];
  // ...
  return values;
}

I'm not quite sure how you formatted your CSV files; my best guess would be:

function parseCSV(string) {
  return string.split("\n").map(function (row) {
    return row.split(",");
  });
}

You may need to worry about extra whitespace.

---

If you're not familiar with JavaScript or programming, this would be a great opportunity to learn. However, if loading the csv files isn't a dire need, just note that it may take you 5-20 hours to learn how to set up the script. Either way, good luck!

Answer 2

I like the Google Apps Script solution I found at digital inspiration.

function importCSVFromGoogleDrive() {
  var file = DriveApp.getFilesByName("filename").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = SpreadsheetApp.getActive().getSheetByName("sheetname")
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
}

This differs from the IMPORTDATA() spreadsheet function in that it will overwrite data already present in your spreadsheet. The first time you run the function, you will need to authorize it to read your Drive files and modify your spreadsheet. After you authorize it, you can add a trigger to make it run every time you open the spreadsheet for editing.