35109

Access, parse, and write .csv data - google apps script

Question:

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 <strong><em>i can't get it to write to the spreadsheet.</em></strong> 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); }

Answer1:

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...

<hr />

<strong>EDIT :</strong>

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 ); }

Recommend

  • Update interval of Maven Central Repo
  • Dynamic function calling Angular 2
  • externalinterface - calling javascript from SWF
  • allow users to “extend” API functions
  • pass argument to function passed as argument to jQuery function
  • Rewrite serial port communicationf rom C++ to C#
  • google-apps script user must give permission on OWN drive to open spreadsheet in app-server's d
  • “projectId must match the following pattern” exception when running Objectify on Google Cloud tools
  • How to connect css to html in Google Apps Scripts
  • Where does the ServletContext.log messages go in tomcat 7?
  • maven-cobertura-plugion does not show the sources [closed]
  • How do I get rid of the Eclipse Find/Replace Bar
  • Plot a CSV file where the delimiter is '; ' (semicolon + space)
  • CSV and MS-DOS CSV Formats
  • ValueError: Found arrays with inconsistent numbers of samples
  • How to remove comma or any characters from Python dataframe column name
  • How to use ResourceDictionary in Windows Phone class library project
  • SSH in Bash Script Messing Up File Read
  • Titanium doesn't recognize Android SDK on Windows
  • How to move an element within a structure, possibly with zippers?
  • How can I get the choice “H2” back in the H2 consol?
  • How do I shift the decimal place in Python?
  • perl, mysql - fasting way to upload a csv file into mysql?
  • Bypass multiple inheritance in Java
  • wxPython: displaying multiple widgets in same frame
  • Textfile Structure (tables)
  • azure media services - The request body is too large and exceeds the maximum permissible limit
  • Read a local file using javascript
  • Django: Count of Group Elements
  • Deleting and Updating values from a cusrsor adapter
  • Window Size for Mac application
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • SQL merge duplicate rows and join values that are different
  • coudnt use logback because of log4j
  • embed rChart in Markdown
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app