访问，解析和写入.csv数据 - google应用脚本 [英] Access, parse, and write .csv data - google apps script
本文介绍了访问，解析和写入.csv数据 - google应用脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在google电子表格中运行javascript。我试图上传一个.cs文件，解析它，并将其写入电子表格。 （这是在将.csv数据写入电子表格之前对其进行操作的初步步骤）。 .csv已成功上传到Google文档列表，但 我无法将其写入电子表格。 我在测试天气时遇到问题，实际上是获取.csv数据，所以我可以解析它。我的脚本包括在下面。我真的会贬低任何建议。我试过几种变化，当前一个是抛出一个csvFile is undefined的错误。我不知道 var files = DocsList.getFiles（fileBlob）; var csvFile =; csvFile = files.getContentAsString（）; 正在工作。如何测试这个？运行google调试器总是最终我的变量都未定义，因为没有用户正在上传文件。谢谢你看。 -Jamie
//检索活动电子表格中包含数据的所有行，并记录
//的值每行。
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）;
}
};
//向活动电子表格添加自定义菜单，其中包含用于调用上述readRows（）函数的单个菜单项
//。
function onOpen（）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）;
var entries =
[{name：Read Data，functionName：readRows}，
{name：Upload DSR，functionName：doGet}];
sheet.addMenu（Du-par's，entries）;
};
//创建菜单以定位.CSV
function doGet（e）{
var app = UiApp.createApplication（）。setTitle上传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;
}
//将.CSV上传到Cloud
函数doPost（e）{
//返回的数据是FileUpload窗口小部件的blob $ b var fileBlob = e.parameter.thefile;
var doc = DocsList.createFile（fileBlob）;
var app = UiApp.getActiveApplication（）;
//显示确认消息
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
}
函数CSVToArray（strData，strDelimiter）{
//查看是否定义了分隔符。如果没有，
//然后默认为逗号。
strDelimiter =（strDelimiter ||，）;
//创建一个正则表达式来解析CSV值。
var objPattern = new RegExp（
（
// Delimiters。
（\\+ strDelimiter +| \\r？\\\\
| \\r | ^）+
//引号字段。
（？：\（[^ \] * \\[^ \] *）*）\|+
//标准字段
（[^ \\\+ strDelimiter + \\r\\\\
] *））
），
gi
）;
//创建给数组
//默认为空第一行
var arrData = [[]];
//创建一个数组来保存我们的数据个别模式
//匹配组
var arrMatches = null;
//保持循环遍历正则表达式匹配
//直到我们可以不再找到匹配
while（arrMatches = objPattern.exec（strData））{
//获取找到的分隔符
var strMatchedDelimiter = arrMatches [1] ;
//检查给定的分隔符的长度是否为
//（不是字符串的开头），如果匹配
//字段分隔符。如果id不，那么我们知道
//这个分隔符是一个行分隔符。
if（
strMatchedDelimiter.length&
（strMatchedDelimiter！= strDelimiter）
）{
//因为我们到达了一个新行的数据，
//向我们的数据数组添加一个空行。
arrData.push（[]）;
}
//现在我们已经有了分隔符了，
//让我们检查一下我们的值b $ b // capture（quoted或unquoted）。
if（arrMatches [2]）{
//我们找到一个引用的值。当我们捕获
//这个值，unescape任何双引号。
var strMatchedValue = arrMatches [2] .replace（
new RegExp（\\，g），
\
）;
} else {
//我们发现了一个非引用的值
var strMatchedValue = arrMatches [3];
}
//现在我们有了值字符串，让我们将
//加到数据数组中
arrData [arrData.length - 1] .push （strMatchedValue）;
}
//返回解析的数据
return（arrData）;
//使用活动工作表可以用几种其他方式拉起一张表
//SpreadsheetApp.getActiveSheet（）
// .getRange（1，1，values.length，values [0] .length）
// .setValues（values）;
}
解决方案
p>
function onOpen （）{
var sheet = SpreadsheetApp.getActiveSpreadsheet（）;
var entries =
[{name：Read Data，functionName：readRows}，
{name：Upload DSR，functionName：doGet}];
sheet.addMenu（Du-par's，entries）;
};
//向活动电子表格添加自定义菜单，其中包含用于调用上述readRows（）函数的单个菜单项
//。
//创建菜单以查找.CSV
函数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;
}
//将.CSV上传到Cloud
function doPost（e）{
var app = UiApp.getActiveApplication（）;
//返回的数据是FileUpload窗口小部件的一个blob
var fileBlob = e.parameter.thefile;
var doc = DocsList.createFile（fileBlob）;
//显示确认消息
var label = app.createLabel（'file uploaded successfully'）;
app.add（label）;
var files = DocsList.getFileById（doc.getId（））; //获取文档的数据，你需要使用正确的方法... fileBlob不是正确的参数
var csvFile = ; //这部分不在doPost函数内部，它不包含在任何函数中，因此试图在每次调用任何函数时运行，包括onOpen（）...要小心{} ...
csvFile = files.getContentAsString（）;
var csvData = CSVToArray（csvFile，，）;
Logger.log（csvData）
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getActiveSheet（）;
for（var i = 0; i
}
return app; //你不需要重新调用show方法，只需返回活动的应用程序来更新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）;
}
};
函数CSVToArray（strData，strDelimiter）{
Logger.log（strData）;
//检查是否定义了定界符。如果没有，
//然后默认为逗号。
strDelimiter =（strDelimiter ||，）;
//创建一个正则表达式来解析CSV值。
var objPattern = new RegExp（
（
// Delimiters。
（\\+ strDelimiter +| \\r？\\\\
| \\r | ^）+
//引号字段。
（？：\（[^ \] * \\[^ \] *）*）\|+
//标准字段
（[^ \\\+ strDelimiter + \\r\\\\
] *））
），
gi
）;
//创建给数组
//默认为空第一行
var arrData = [[]];
//创建一个数组来保存我们的数据个别模式
//匹配组
var arrMatches = null;
//保持循环遍历正则表达式匹配
//直到我们可以不再找到匹配
while（arrMatches = objPattern.exec（strData））{
//获取找到的分隔符
var strMatchedDelimiter = arrMatches [1] ;
//检查给定的分隔符的长度是否为
//（不是字符串的开头），如果匹配
//字段分隔符。如果id不，那么我们知道
//这个分隔符是一个行分隔符。
if（
strMatchedDelimiter.length&
（strMatchedDelimiter！= strDelimiter）
）{
//因为我们到达了一个新行的数据，
//向我们的数据数组添加一个空行。
arrData.push（[]）;
}
//现在我们已经有了分隔符了，
//让我们检查一下我们的值b $ b // capture（quoted或unquoted）。
if（arrMatches [2]）{
//我们找到一个引用的值。当我们捕获
//这个值，unescape任何双引号。
var strMatchedValue = arrMatches [2] .replace（
new RegExp（\\，g），
\
）;
} else {
//我们发现了一个非引用的值
var strMatchedValue = arrMatches [3];
}
//现在我们有了值字符串，让我们将
//加到数据数组中
arrData [arrData.length - 1] .push （strMatchedValue）;
}
//返回解析的数据
return（arrData）;
//使用活动工作表可以用几种其他方式拉起一张表
//SpreadsheetApp.getActiveSheet（）
// .getRange（1,1，values.length，values [0] .length）
// .setValues（values）;
}
可以使用 return app.close（）; 自动关闭UI，无需显示带有上传完成的标签，工作表显示数据，因此我们知道上传过程正确...
$ b b $ b
这里是一个紧凑版本的代码，没有大量的注释和一些改进，注意我使用 setValues（）将数组数据写回电子表格的方式无循环。
我在您的其他帖子中看到您希望从驱动器中删除上传的文件，因此我直接通过获取字符串内容跳过了此步骤（参见代码中的注释）
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
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（）; //无需创建中间文件
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）;
}
};
函数CSVToArray（strData，strDelimiter）{
Logger.log（strData）;
strDelimiter =（strDelimiter ||，）;
var objPattern = new RegExp（
（
（\\+ strDelimiter +| \\r？\\\\
| \\r | ^ ）+
（？：\（[^ \] *（？：\\[^ \] *）*）\|+
（[^ \\\+ strDelimiter +\\r\\\\
] *））
），
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（
newRegExp（\\，g），
\
）;
} else {
var strMatchedValue = arrMatches [3];
}
arrData [arrData.length - 1] .push（strMatchedValue）;
}
return（arrData）;
}
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);
}
解决方案
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 );
}
这篇关于访问，解析和写入.csv数据 - google应用脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
