CODEWITHSUNDEEP
google-apps-script
Jamie Marshall
Jamie Marshall

Reputation: 2294

Use .csv data in a spreadsheet directly from a local drive

I'm trying to write a script to directly upload data from a .csv file stored on a local hard drive to a google spreadsheet without placing the .csv into the google docs list.

I realize that google spreadsheets already has a function for doing this, but my end game here is to be able to upload data daily from a .csv on a local drive, to my script, sort through it, and return only the necessary information to a spreadsheet.

Currently I'm trying to test my readDSR (should be reading the uploaded .csv)function by returning the .csv data as a string in my spreadsheet. However it won't work. I would really appreciate some help on this one. My code is below

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = 
      [ {name:"Read Data", functionName:"readRows"},
        {name:"Upload DSR", functionName:"findDSR()"} ];

  sheet.addMenu("Du-par's", entries);
};

function findDSR() {
  var openFile = UiApp.createApplication();
  var formContent = openFile.createVerticalPanel();
  formContent.add(openFile.createFileUpload().setName("DSRfile"));
  formContent.add(openFile.createSubmitButton("Start Upload"));
  var form = openFile.createFormPanel();
  form.add(formContent);
  openFile.add(form);
  SpreadsheetApp.getActiveSpreadsheet().show(openFile);

}

function readDSR(e) {
  DSR = e.parameter.DSRfile;
  csvFile = DSR.getContentAsString();
  var sheet = getActiveSheet();
  var lastRow = sheet.getLastRow()+1;
  var lastCell = sheet.getLastCell("A"+lastRow);
  lastCell.setValue = csvFile;
  }
}

Edit, Sep 14 at 22:48

Awesome, thanks for the quick reply. Here is my new script given the information in the other post you directed me to. For some reason I'm still getting an error, but I understand the concept now. I keep getting

"Error encountered: An unexpected error has occurred."

This should be giving me an "upload complete" message. Could the problem be that I'm executing the script inside of a spreadsheet? I'm fairly sure the problem must be in the "readDSR" function.

// Create Menu to Locate .CSV
function findDSR(e) {
  var app = UiApp.createApplication();
  var formContent = app.createVerticalPanel();
  formContent.add(app.createFileUpload().setName("DSRfile"));
  formContent.add(app.createSubmitButton("Start Upload"));
  var form = app.createFormPanel();
  form.add(formContent);
  app.add(form);
  SpreadsheetApp.getActiveSpreadsheet().show(app);

}

// Upload .CSV file
function readDSR(e)
{
  var DSRload = e.parameter.DSRfile;
  var Doc = DriveApp.createFile("DSRload");

  var app = UiApp.getActiveAplication();

  // Display a confirmation Messege
  var label = app.createLable("File Upload Successful");
  app.add(label);

  DocID = getDocId();
  MakeTranslationDoc = DocID;

  return app();
}

Upvotes: 3

Views: 2655

Answers (2)

Serge insas
Serge insas

Reputation: 46794

These answers don't work for a simple reason : to use a fileUpload widget it must be part of a doGet/doPost Ui design therefor the 'unexpected error' Try like this for example (simplified version) : 

function doGet() {
  var app = UiApp.createApplication();
  var formContent = app.createVerticalPanel();
  formContent.add(app.createFileUpload().setName("DSRfile"));
  formContent.add(app.createSubmitButton("Start Upload"));
  var form = app.createFormPanel();
  form.add(formContent);
  app.add(form);
  SpreadsheetApp.getActiveSpreadsheet().show(app);

}

function doPost(e){
  var DSRload = e.parameter.DSRfile;
  var Doc = DriveApp.createFile(DSRload);
  DocID = Doc.getId();
//  MakeTranslationDoc = DocID;
  var app = UiApp.getActiveApplication();

  // Display a confirmation Message
  var label = app.createLabel("File Upload Successful");
  var clickHandler = app.createServerHandler('close');
  app.add(label).add(app.createButton('close this window', clickHandler));
  return app;
}

function close(){
  return UiApp.getActiveApplication().close();
}

Upvotes: 0

patt0
patt0

Reputation: 800

It is common theme, uploading a file to Drive using a simple UI form and then wanting to do some work with it, it seems that may be broken at this stage in GAS. The work around consists in using the Drive API to access the file you have just uploaded and get the data using that mechanism.

Please read this post in detail, as I have detailed the way to do this. It is a similar problem to yours, except with a text file, and we should be able to extend it to a csv file with out any issues.

Create new Doc in Google drive after processing uploaded text file

Let me know how that works for you

Upvotes: 1

Related Questions