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 ma.
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);
}
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 ma.
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);
}
- I’m voting to close this question because it's about using deprecated Google Apps Script services (UiApp and DocList) – Wicket Commented Apr 18, 2020 at 19:10
1 Answer
Reset to default 4there were a few errors in your code. Here is a working version with ments 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 ma.
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 pleted" , the sheet shows the data so we know the upload process went right...
EDIT :
And here is a "pact" version of your code without the numerous ments 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 ment 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 );
}