0

I'm running javascript inside a google spreadsheet. I'm trying to upload a .cs file, parse it, and write it to a spreadsheet. (this is a preliminary step to being able to operate on the .csv data before writing it to the spreadsheet). The .csv is successfully uploading to the Google Docs list, but i can't get it to write to the spreadsheet. I'm having trouble testing weather or not my script is actually getting the .csv data so I can parse it. My script is included below. I would really apreciate any advice. I've tried several variations, the current one is throwing a "csvFile is undefined" error. I don't know if var files = DocsList.getFiles(fileBlob); var csvFile = ""; csvFile = files.getContentAsString(); is working. How do I test this? running the google debugger always ends up with my variables all being undefined because no user is uploading a file. Thanks for looking. -Jamie

// Retrieves all the rows in the active spreadsheet that contain data and logs the
// values for each row.
function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};


//Adds a custom menu to the active spreadsheet, containing a single menu item
//for invoking the readRows() function specified above.

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

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


// Create Menu to Locate .CSV
 function doGet(e) {

 var app = UiApp.createApplication().setTitle("Upload DSR");
   var formContent = app.createVerticalPanel();
   formContent.add(app.createFileUpload().setName('thefile'));
   formContent.add(app.createSubmitButton("Submit DSR"));
   var form = app.createFormPanel();
   form.add(formContent);
   app.add(form);
   SpreadsheetApp.getActive().show(app);
   return app;
   }

// Upload .CSV to Cloud
 function doPost(e) {

// data returned is a blob for FileUpload widget
   var fileBlob = e.parameter.thefile;
   var doc = DocsList.createFile(fileBlob); 

   var app = UiApp.getActiveApplication();

  // Display a confirmation message
     var label = app.createLabel('file uploaded successfully');
   app.add(label);
   SpreadsheetApp.getActive().show(app);

     var files = DocsList.getFiles(fileBlob);
     var csvFile = "";
     csvFile = files.getContentAsString();
     }

  var csvData = CSVToArray(csvFile, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }


 function CSVToArray( strData, strDelimiter ){

  // Check to see if the delimiter is defined. If not,
  // then default to comma.
  strDelimiter = (strDelimiter || ",");

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );


  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;


  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){

    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];

    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }


    // Now that we have our delimiter out of the way,
    // let's check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){

      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );

    } else {

      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];

    }


    // Now that we have our value string, let's add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

  // Return the parsed data.
  return( arrData );

  // Using active sheet here, but you can pull up a sheet in several other ways as well
  //SpreadsheetApp.getActiveSheet()
    //            .getRange( 1, 1, values.length, values[0].length )
      //          .setValues(values);
} 
1
  • I’m voting to close this question because it's about using deprecated Google Apps Script services (UiApp and DocList) Commented Apr 18, 2020 at 19:10

1 Answer 1

4

there were a few errors in your code. Here is a working version with comments on the lines I modified.

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

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

//Adds a custom menu to the active spreadsheet, containing a single menu item
//for invoking the readRows() function specified above.

// Create Menu to Locate .CSV
function doGet(e) {

  var app = UiApp.createApplication().setTitle("Upload DSR");
  var formContent = app.createVerticalPanel();
  formContent.add(app.createFileUpload().setName('thefile'));
  formContent.add(app.createSubmitButton("Submit DSR"));
  var form = app.createFormPanel();
  form.add(formContent);
  app.add(form);
  SpreadsheetApp.getActive().show(app);
  return app;
}

// Upload .CSV to Cloud
function doPost(e) {
  var app = UiApp.getActiveApplication();  
  // data returned is a blob for FileUpload widget
  var fileBlob = e.parameter.thefile;
  var doc = DocsList.createFile(fileBlob); 
  // Display a confirmation message
  var label = app.createLabel('file uploaded successfully');
  app.add(label);  
  var files = DocsList.getFileById(doc.getId());// to get the document's data back you need to use the right method... fileBlob was not the right argument
  var csvFile = ""; // this part was not inside the doPost function, it wasn't included in any function thus trying to run each time any function is called, includind onOpen()... beware the {}...
  csvFile = files.getContentAsString();
  var csvData = CSVToArray(csvFile, ",");
  Logger.log(csvData)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
  return app;// you don't need to re-call the show method, just return the active app to update the UI
}


function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};



function CSVToArray( strData, strDelimiter ){
Logger.log(strData);
  // Check to see if the delimiter is defined. If not,
  // then default to comma.
  strDelimiter = (strDelimiter || ",");

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );


  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;


  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){

    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];

    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }


    // Now that we have our delimiter out of the way,
    // let's check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){

      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );

    } else {

      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];

    }


    // Now that we have our value string, let's add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

  // Return the parsed data.
  return( arrData );

  // Using active sheet here, but you can pull up a sheet in several other ways as well
  //SpreadsheetApp.getActiveSheet()
    //            .getRange( 1, 1, values.length, values[0].length )
      //          .setValues(values);
} 

at the end of the doPost function you could use return app.close(); to automatically close the UI, no need to show a label with "upload completed" , the sheet shows the data so we know the upload process went right...


EDIT :

And here is a "compact" version of your code without the numerous comments and with a few improvements, notice the way I write the array data back to the spreadsheet using setValues() without loop. The UI is also slightly modified.

I saw in your other post that you wanted to delete the uploaded file from your drive so I skipped this step by getting the string content directly from the blob (see comment in code)

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

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

function doGet(e) {  
  var app = UiApp.createApplication().setTitle("Upload DSR").setHeight(100).setWidth(300);
  var formContent = app.createGrid(3,1);
  formContent.setWidget(0,0,app.createFileUpload().setName('thefile'));
  formContent.setWidget(2,0,app.createSubmitButton("Submit DSR"));
  var form = app.createFormPanel();
  form.add(formContent);
  app.add(form);
  SpreadsheetApp.getActive().show(app);
  return app;
}

function doPost(e) {
  var app = UiApp.getActiveApplication();  
  var fileBlob = e.parameter.thefile;
  var content = fileBlob.getDataAsString();// no need to create an intermediate file
  var csvData = CSVToArray(content, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  sheet.getRange(1,1,csvData.length,csvData[0].length).setValues(csvData);
  return app.close();
}

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};


function CSVToArray( strData, strDelimiter ){
  Logger.log(strData);
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  var arrData = [[]];
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){
      arrData.push( [] );
    }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
} 
Sign up to request clarification or add additional context in comments.

1 Comment

FYI, these days you can use "Utilities.parseCsv' (developers.google.com/apps-script/reference/utilities/…) rather than having to roll your own CSVToArray fuction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.