如何自动将上传的CSV或XLS文件中的数据导入Google表格 [英] How to automatically import data from uploaded CSV or XLS file into Google Sheets
本文介绍了如何自动将上传的CSV或XLS文件中的数据导入Google表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在一台生成CSV或XLS报告到Google云端硬盘文件夹的服务器上有一个遗留数据库系统（不能访问网络）。目前，我手动在Drive web界面中打开这些文件，并将它们转换为Google表格。
我宁愿将其设置为自动，以便创建追加/转换并在其他工作表中绘制数据。
是否可以输出原生.gsheet文件？或者有没有办法将编辑后的CSV或XLS格式的文件转换成.gsheet格式，或者通过Google Apps或通过基于Windows的脚本/实用程序将其保存到Google Drive？
您可以使用Google Apps脚本以编程方式将数据从您的云端硬盘中的csv文件导入到现有Google表格中，并根据需要替换/追加数据。
以下是一些示例代码。它假设： a）您的云端硬盘中有一个指定的文件夹，其中CSV文件被保存/上传到; b） CSV文件名为report.csv，其中的数据以逗号分隔;和 c）将CSV数据导入到指定的电子表格中。
函数importData（）{
var fSource = DriveApp.getFolderById（reports_folder_id） ; // reports_folder_id =保存csv报告的文件夹的ID
var fi = fSource.getFilesByName（'report.csv'）; //最新的报告文件
var ss = SpreadsheetApp.openById（data_sheet_id）; // data_sheet_id =包含要用新报告数据更新的数据的电子表格的ID
if（fi.hasNext（））{//如果报告文件夹中存在report.csv文件，则继续
var file = fi.next（）;
var csv = file.getBlob（）。getDataAsString（）;
var csvData = CSVToArray（csv）; //见下面的CSVToArray函数
var newsheet = ss.insertSheet（'NEWDATA'）; //创建一个'NEWDATA'表来存储导入的数据
//通过csv数据数组循环并插入（追加）到'NEWDATA'表中
for（var i = 0，lenCsv = csvData。 length; i< lenCsv; i ++）{
newsheet.getRange（i + 1,1,1，csvData [i] .length）.setValues（new Array（csvData [i]））;
}
/ *
**报告数据现在位于电子表格中的NEWDATA表单中 - 根据需要处理它，
**，然后使用ss删除NEWDATA表单。 deleteSheet（newsheet）
* /
//重命名report.csv文件，以便在下次计划运行时不处理它
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
//这会将一个分隔字符串解析为一个
//数组的数组。默认的分隔符是逗号，但是这个
//可以在第二个参数中被覆盖。
函数CSVToArray（strData，strDelimiter）{
//检查是否定义了分隔符。如果不是，
//则默认为COMMA。
strDelimiter =（strDelimiter ||，）;
//创建一个正则表达式来解析CSV值。
var objPattern = new RegExp（
（
//分隔符。
（\\+ strDelimiter +| \\r？\\\\
| \\r | ^）+
//引用字段
（？：\（[^ \] *（?: \\\ \\[^ \] *）*）\|+
//标准字段
（[^ \\\+ strDelimiter + \\r\\\\
] *））
），
gi
）;
//创建一个数组来保存我们的数据给数组
//默认为空的第一行
var arrData = [[]];
//创建一个数组来保存我们的单个模式
//匹配组
var arrMatches = null;
//循环遍历正则表达式匹配
//直到找不到匹配为止
while（arrMatches = objPattern.exec（strData））{
//获取发现的分隔符
var strMatchedDelimiter = arrMatches [1];
//检查一下，看看这个giv en分隔符的长度为
//（不是字符串的开头），如果它匹配
//字段分隔符。如果id不是，那么我们知道
//这个分隔符是行分隔符。
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）;
};
然后，您可以创建时间 - 驱动触发器，以定期运行 importData（）函数（例如每晚1AM），所以你所要做的就是把新的report.csv文件保存到指定的Drive文件夹中，并在下次计划运行时自动处理。
如果您绝对使用您必须使用Excel文件而不是CSV，然后您可以使用下面的代码。要使用它，您必须在脚本和开发人员控制台中的高级Google服务中启用云端硬盘API（请参阅如何启用高级服务）。
/ **
*将Excel文件转换为表格
* @param {Blob} excelFile Excel文件blob数据;必需
* @param {字符串}文件名在上传驱动器上的文件名;必需
* @param {Array} arrParents将转换后的文件放入文件夹ID的数组;可选，将默认为驱动器根文件夹
* @return {Spreadsheet}转换Google Spreadsheet实例
** /
函数convertExcel2Sheets（excelFile，filename，arrParents）{
var父母= arrParents || []; //检查是否提供了可选的arrParents参数，如果不是
，则默认为空数组if（！parents.isArray）parents = []; //确保父母是一个数组，如果不是
，则将其重置为空数组//请参阅https://developers.google.com/drive/web/manage-上传＃简单）
var uploadParams = {
方法：'post'，
contentType：'application / vnd.ms-excel'，//适用于.xls和.xlsx文件
contentLength：excelFile.getBytes（）。length，
headers：{'Authorization'：'Bearer'+ ScriptApp.getOAuthToken（）}，
payload：excelFile.getBytes（）
};
//将文件上传到云端硬盘根文件夹并转换为表格
var uploadResponse = UrlFetchApp.fetch（'https://www.googleapis.com/upload/drive/v2/files/ ？uploadType = media& convert = true'，uploadParams）;
//解析上传和转换响应数据（需要这个能够获得转换后的表的ID）
var fileDataResponse = JSON.parse（uploadResponse.getContentText（））;
//创建用于更新转换文件名称和父文件夹的有效数据（body）数据
var payloadData = {
title：filename，
parents：[ ]
};
if（parents.length）{//将提供的父文件夹id添加到payloadData，如果有任何
for（var i = 0; i< parents.length; i ++）{
试试{
var folder = DriveApp.getFolderById（parents [i]）; //检查该文件夹ID是否存在于驱动器中，并且用户可以写入它
payloadData.parents.push（{id：parents [i]}）;
}
catch（e）{} //如果在Drive
}
}
中不存在这样的文件夹ID，则默认失败// Drive API文件更新请求的参数（请参阅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）
};
//更新已转换图纸的元数据（文件名和父文件夹）
UrlFetchApp.fetch（'https://www.googleapis.com/drive/v2/files/' + fileDataResponse.id，updateParams）;
返回SpreadsheetApp.openById（fileDataResponse.id）;
$ b $ **
* convertExcel2Sheets（）用于测试
** /
函数的示例用法testConvertExcel2Sheets（）{
var xlsId =0B9 ************** OFE; //将Excel文件的ID转换为
var xlsFile = DriveApp.getFileById（xlsId）; // Excel文件的文件实例
var xlsBlob = xlsFile.getBlob（）; //用于转换的Excel文件的Blob源
var xlsFilename = xlsFile.getName（）; //文件名给予转换后的文件;默认与源文件相同
var destFolders = []; //将转换文件放入的Drive文件夹的ID数组;空数组=根文件夹
var ss = convertExcel2Sheets（xlsBlob，xlsFilename，destFolders）;
Logger.log（ss.getId（））;
}
I have a legacy database system (not web accessible) on a server which generates CSV or XLS reports to a Google Drive folder. Currently, I am manually opening those files in Drive web interface and converting them to Google Sheets.
I would rather this be automatic so that I can create jobs that append/transform and graph the data in other sheets.
Is it possible to output a native .gsheet file? Or is there a way to convert CSV or XLS to .gsheet programmatically after saving it to Google Drive either in Google Apps or via a Windows based script/utility?
解决方案
You can programmatically import data from a csv file in your Drive into an existing Google Sheet using Google Apps Script, replacing/appending data as needed.
Below is some sample code. It assumes that: a) you have a designated folder in your Drive where the CSV file is saved/uploaded to; b) the CSV file is named "report.csv" and the data in it comma-delimited; and c) the CSV data is imported into a designated spreadsheet. See comments in code for further details.
function importData() {
var fSource = DriveApp.getFolderById(reports_folder_id); // reports_folder_id = id of folder where csv reports are saved
var fi = fSource.getFilesByName('report.csv'); // latest report file
var ss = SpreadsheetApp.openById(data_sheet_id); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data
if ( fi.hasNext() ) { // proceed if "report.csv" file exists in the reports folder
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function
var newsheet = ss.insertSheet('NEWDATA'); // create a 'NEWDATA' sheet to store imported data
// loop through csv data array and insert (append) as rows into 'NEWDATA' sheet
for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
}
/*
** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
*/
// rename the report.csv file so it is 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
// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.
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 );
};
You can then create time-driven trigger in your script project to run importData() function on a regular basis (e.g. every night at 1AM), so all you have to do is put new report.csv file into the designated Drive folder, and it will be automatically processed on next scheduled run.
If you absolutely MUST work with Excel files instead of CSV, then you can use this code below. For it to work you must enable Drive API in Advanced Google Services in your script and in Developers Console (see How to Enable Advanced Services for details).
/**
* Convert Excel file to Sheets
* @param {Blob} excelFile The Excel file blob data; Required
* @param {String} filename File name on uploading drive; Required
* @param {Array} arrParents Array of folder ids to put converted file in; Optional, will default to Drive root folder
* @return {Spreadsheet} Converted Google Spreadsheet instance
**/
function convertExcel2Sheets(excelFile, filename, arrParents) {
var parents = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not
if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not
// Parameters for 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 for both .xls and .xlsx files
contentLength: excelFile.getBytes().length,
headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
payload: excelFile.getBytes()
};
// Upload file to Drive root folder and convert to Sheets
var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);
// Parse upload&convert response data (need this to be able to get id of converted sheet)
var fileDataResponse = JSON.parse(uploadResponse.getContentText());
// Create payload (body) data for updating converted file's name and parent folder(s)
var payloadData = {
title: filename,
parents: []
};
if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any
for ( var i=0; i<parents.length; i++ ) {
try {
var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it
payloadData.parents.push({id: parents[i]});
}
catch(e){} // fail silently if no such folder id exists in Drive
}
}
// Parameters for 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 and parent folder(s)) of converted sheet
UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);
return SpreadsheetApp.openById(fileDataResponse.id);
}
/**
* Sample use of convertExcel2Sheets() for testing
**/
function testConvertExcel2Sheets() {
var xlsId = "0B9**************OFE"; // ID of Excel file to convert
var xlsFile = DriveApp.getFileById(xlsId); // File instance of Excel file
var xlsBlob = xlsFile.getBlob(); // Blob source of Excel file for conversion
var xlsFilename = xlsFile.getName(); // File name to give to converted file; defaults to same as source file
var destFolders = []; // array of IDs of Drive folders to put converted file in; empty array = root folder
var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);
Logger.log(ss.getId());
}
The above code is also available as a gist here.
这篇关于如何自动将上传的CSV或XLS文件中的数据导入Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
