如何将上传的 CSV 或 XLS 文件中的数据自动导入 Google 表格 [英] How to automatically import data from uploaded CSV or XLS file into Google Sheets
问题描述
我在服务器上有一个旧的数据库系统(不能通过网络访问)，它可以将 CSV 或 XLS 报告生成到 Google Drive 文件夹.目前，我正在云端硬盘网络界面中手动打开这些文件并将它们转换为 Google 表格.
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.
是否可以输出原生 .gsheet 文件?或者，在将 CSV 或 XLS 保存到 Google 云端硬盘后，是否可以通过 Google Apps 或基于 Windows 的脚本/实用程序以编程方式将其转换为 .gsheet?
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?
推荐答案
您可以使用 Google Apps 脚本以编程方式将数据从云端硬盘中的 csv 文件导入到现有的 Google 表格中，并根据需要替换/附加数据.
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.
下面是一些示例代码.它假设:a)您的云端硬盘中有一个指定的文件夹，用于保存/上传 CSV 文件；b) CSV 文件名为report.csv"，其中的数据以逗号分隔；和 c) CSV 数据被导入到指定的电子表格中.有关详细信息，请参阅代码中的注释.
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 );
};
然后您可以在脚本项目中创建时间驱动触发器定期运行 importData() 函数(例如每晚凌晨 1 点)，因此您只需将新的 report.csv 文件放入指定的 Drive 文件夹中，它将在下一次预定运行.
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.
如果您绝对必须使用 Excel 文件而不是 CSV，那么您可以使用下面的代码.要使其工作，您必须在脚本和开发人员控制台中的高级 Google 服务中启用 Drive API(请参阅 如何启用高级服务了解详情).
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());
}
这篇关于如何将上传的 CSV 或 XLS 文件中的数据自动导入 Google 表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
