How to automatically import data from uploaded CSV or XLS file into Google Sheets -

How to automatically import data from uploaded CSV or XLS file into Google Sheets -

i have legacy database scheme (not web accessible) on server generates csv or xls reports google drive. currently, manually opening files in drive web interface , converting them google sheets.

i rather automatic can create jobs append/transform , graph info in other sheets.

is possible output native .gsheet file? or there way convert file automatically after saving google drive?

you can programmatically import info csv file in drive existing google sheet using google apps script, replacing/appending info needed.

below sample code. assumes that: a) have designated folder in drive csv file saved/uploaded to; b) csv file named "report.csv" , info in comma-delimited; , c) csv info imported designated spreadsheet. see comments in code farther details.

function importdata() { var fsource = driveapp.getfolderbyid(reports_folder_id); // reports_folder_id = id of folder csv reports saved var fi = fsource.getfilesbyname('report.csv'); // latest study file var ss = spreadsheetapp.openbyid(data_sheet_id); // data_sheet_id = id of spreadsheet holds info updated new study info if ( fi.hasnext() ) { // proceed if "report.csv" file exists in reports folder var file = fi.next(); var csv = file.getblob().getdataasstring(); var csvdata = csvtoarray(csv); // see below csvtoarray function var newsheet = ss.insertsheet('newdata'); // create 'newdata' sheet store imported info // loop through csv info array , insert (append) rows 'newdata' sheet ( var i=0, lencsv=csvdata.length; i<lencsv; i++ ) { newsheet.getrange(i+1, 1, 1, csvdata[i].length).setvalues(new array(csvdata[i])); } /* ** study info in 'newdata' sheet in spreadsheet - process needed, ** delete 'newdata' sheet using ss.deletesheet(newsheet) */ // rename report.csv file not processed on next scheduled run file.setname("report-"+(new date().tostring())+".csv"); } }; // http://www.bennadel.com/blog/1504-ask-ben-parsing-csv-strings-with-javascript-exec-regular-expression-command.htm // parse delimited string array of // arrays. default delimiter comma, // can overriden in sec argument. function csvtoarray( strdata, strdelimiter ) { // check see if delimiter defined. if not, // default comma. strdelimiter = (strdelimiter || ","); // create regular look parse csv values. var objpattern = new regexp( ( // delimiters. "(\\" + strdelimiter + "|\\r?\\n|\\r|^)" + // quoted fields. "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + // standard fields. "([^\"\\" + strdelimiter + "\\r\\n]*))" ), "gi" ); // create array hold our data. give array // default empty first row. var arrdata = [[]]; // create array hold our individual pattern // matching groups. var arrmatches = null; // maintain looping on regular look matches // until can no longer find match. while (arrmatches = objpattern.exec( strdata )){ // delimiter found. var strmatcheddelimiter = arrmatches[ 1 ]; // check see if given delimiter has length // (is not start of string) , if matches // field delimiter. if id not, know // delimiter row delimiter. if ( strmatcheddelimiter.length && (strmatcheddelimiter != strdelimiter) ){ // since have reached new row of data, // add together empty row our info array. arrdata.push( [] ); } // have our delimiter out of way, // let's check see kind of value // captured (quoted or unquoted). if (arrmatches[ 2 ]){ // found quoted value. when capture // value, unescape double quotes. var strmatchedvalue = arrmatches[ 2 ].replace( new regexp( "\"\"", "g" ), "\"" ); } else { // found non-quoted value. var strmatchedvalue = arrmatches[ 3 ]; } // have our value string, let's add together // info array. arrdata[ arrdata.length - 1 ].push( strmatchedvalue ); } // homecoming parsed data. return( arrdata ); };

you can create time-driven trigger in script project run importdata() function on regular basis (e.g. every night @ 1am), have set new report.csv file designated drive folder, , automatically processed on next scheduled run.

if absolutely must work excel files instead of csv, can utilize code below. work must enable drive api in advanced google services in script , in developers console (see how enable advanced services details).

/** * convert excel file sheets * @param {blob} excelfile excel file blob data; required * @param {string} filename file name on uploading drive; required * @param {array} arrparents array of folder ids set converted file in; optional, default drive root folder * @return {spreadsheet} converted google spreadsheet instance **/ function convertexcel2sheets(excelfile, filename, arrparents) { var parents = arrparents || []; // check if optional arrparents argument provided, default empty array if not if ( !parents.isarray ) parents = []; // create sure parents array, reset empty array if not // parameters drive api simple upload request (see https://developers.google.com/drive/web/manage-uploads#simple) var uploadparams = { method:'post', contenttype: 'application/vnd.ms-excel', // works both .xls , .xlsx files contentlength: excelfile.getbytes().length, headers: {'authorization': 'bearer ' + scriptapp.getoauthtoken()}, payload: excelfile.getbytes() }; // upload file drive root folder , convert sheets var uploadresponse = urlfetchapp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadtype=media&convert=true', uploadparams); // parse upload&convert response info (need able id of converted sheet) var filedataresponse = json.parse(uploadresponse.getcontenttext()); // create payload (body) info updating converted file's name , parent folder(s) var payloaddata = { title: filename, parents: [] }; if ( parents.length ) { // add together provided parent folder(s) id(s) payloaddata, if ( var i=0; i<parents.length; i++ ) { seek { var folder = driveapp.getfolderbyid(parents[i]); // check folder id exists in drive , user can write payloaddata.parents.push({id: parents[i]}); } catch(e){} // fail silently if no such folder id exists in drive } } // parameters drive api file update request (see https://developers.google.com/drive/v2/reference/files/update) var updateparams = { method:'put', headers: {'authorization': 'bearer ' + scriptapp.getoauthtoken()}, contenttype: 'application/json', payload: json.stringify(payloaddata) }; // update metadata (filename , parent folder(s)) of converted sheet urlfetchapp.fetch('https://www.googleapis.com/drive/v2/files/'+filedataresponse.id, updateparams); homecoming spreadsheetapp.openbyid(filedataresponse.id); } /** * sample utilize of convertexcel2sheets() testing **/ function testconvertexcel2sheets() { var xlsid = "0b9**************ofe"; // id of excel file convert var xlsfile = driveapp.getfilebyid(xlsid); // file instance of excel file var xlsblob = xlsfile.getblob(); // blob source of excel file conversion var xlsfilename = xlsfile.getname(); // file name give converted file; defaults same source file var destfolders = []; // array of ids of drive folders set converted file in; empty array = root folder var ss = convertexcel2sheets(xlsblob, xlsfilename, destfolders); logger.log(ss.getid()); }

the above code available gist here.

google-apps-script google-spreadsheet google-drive-sdk google-spreadsheet-api