Export CSV data (multiple data rows) to Google Sheet

I have since come up with another method, which helps to reduce the number of calls to the apps script and the spreadsheet

SCRIPT

function doPost(e) {

var data = JSON.parse(e.postData.contents) ; // or >> eval(e.postData.contents) ;

var ss = SpreadsheetApp.getActive();

var sh = ss.getSheetByName('Sheet1');


for (var i=0;i<data.length;i++) {

sh.appendRow(data[i]);

}


return ContentService.createTextOutput("Success") ;

}

If you want to, for what ever reason, upload your data and place it in the next column, instead of the next row, you can use this script:


function doPost(e) {

var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName('Sheet1');
var nc = sh.getLastColumn() + 1;
var rows = JSON.parse(e.postData.contents);
var irl = rows[0].length;

sh.insertColumns(nc,irl);

for (var i=0;i<rows.length;i++) {
var range = sh.getRange(i+1,nc, 1, irl);
range.setValues([rows[i]]);
}

return ContentService.createTextOutput("Success") ;

}

BLOCKS

I took the quick route using appendRow. If we know the size of the data (e.g. number of columns and rows), which we can get with google apps script, then it is possible to paste the entire dataset in one call.

setValues();

ALTERNATE METHOD

From our lists in Appinventor we create suitably formatted strings for each “column” (e.g. ID or NAME) and send the entire string to the webapp as a single parameter. You can of course pull in a csv file using the file component.

The webapp then converts these strings to javascript arrays, and then appends each set to the spreadsheet. Create the web app as a bound script on the google sheet, as opposed to having a separate script project file.

My simple example only handles two columns of data, an ID and a NAME To add more, amend the blocks in AppInventor and edit the web app script (remembering to republish!)

Modern browsers should be able to cope with very long uris, but I have put in a check on the response code just in case. I stress tested the method using a csv table of 6 columns and 100 entries, so 600 pieces of data. The sheet populated in under 5 seconds!

BLOCKS

WEB APP

function doGet(e) {

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName("Sheet1");

uploadData(e,sheet);

}


function doPost(e) {

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName("Sheet1");

uploadData(e,sheet);

}


function uploadData(e,sheet) {


var ids = eval("[" + e.parameters.ids + "]") ;

var names = eval("[" + e.parameters.names + "]") ;


for (var i=0;i<ids.length;i++) {

var id = ids[i];

var name = names[i];

sheet.appendRow([id,name]);

}

}