Google Apps脚本创建excel文件的工作表版本 [英] Google Apps Script creates sheets version of excel file
问题描述
我正在努力实现流程的自动化。目前，我正在将Excel（ .xlsx ）文件上传到云端硬盘中的特定文件夹。然后我手动将它们转换为Google表格文件，然后将其移至单独的文件夹中。我想自动化这个过程。我知道我必须编写脚本来遍历2个文件夹来比较并查看哪些文件尚未转换，然后转换它在两个位置都找不到的文件。然而，我正在努力采取最好的方法来实现这一目标。
下面的代码可能或可能不在正确的轨道上，我对此很感兴趣，真的只是尝试拼凑在一起。
函数Excel2Sheets（）
{
//在
内记录excel文件夹和文件的字符串var excelfolder = DriveApp.getFolderById（'1JbamZxNhAyZT3OifrIstZKyFF_d257mq'）;
var excelfiles = excelfolder.getFiles（）;
//将表单文件夹和文件的字符串记录在
中var sheetfolder = DriveApp.getFolderById（'1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt'）;
var ID = sheetfolder.getId（）;
var sheetfiles = sheetfolder.getFiles（）;
var MType = MimeType.GOOGLE_SHEETS; $（b）b
（excelfiles.hasNext（））{
var excelfile = excelfiles.next（）;
var excelname = excelfile.getName（）; $（）
while（sheetfiles.hasNext（））{
var sheetfile = sheetfiles.next（）;
var sheetname = sheetfile.getName（）;
if（sheetname == excelname）{
break;
if（sheetfiles.hasNext（0））{
var blob = excelfile.getBlob（）;
sheetfolder.createFile（excelname，blob，MType）;
休息;
}
}
}
}
I也玩过这个代码。谢谢
function fileChecker（）
{
try {
//建立Excel源文件夹
var excelfolder = DriveApp.getFolderById（'1JbamZxNhAyZT3OifrIstZKyFF_d257mq'）;
//建立工作表目标文件夹
var sheetfolder = DriveApp.getFolderById（'1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt'）;
//建立返回文件类型
var MType = MimeType.GOOGLE_SHEETS;
//获取excel文件夹中的所有文件
var excelfiles = excelfolder.getFiles（）;
//通过excel文件循环
while（excelfiles.hasNext（））{
//建立特定的excel文件
var excelfile = excelfiles.next（）;
//在工作表文件夹中检查具有相同名称的文件
var sheetfiles = sheetfolder.getFilesByName（excelfile.getName（））;
// Logical Test for file match
if（sheetfiles.hasNext（））{
//获取文件名
var excelname = excelfile.getName（）;
//创建文件Blob
var blob = excelfile.getBlob（）;
//创建具有Excel文件的给定名称和数据的表单文件
sheetfolder.createFile（excelname，blob，MType）;
catch（err）{
Logger.log（err.lineNumber +' - '+ err）;
}
}
你的代码，如果他们到达 createFile 行，应该抛出这个错误：
无效的参数：file.contentType
因为您正在传递 Blob 而 createFile （name，content，mimetype）需要一个 String 。查看DriveApp的参考页面，无疑会注意到 File＃getAs（mimetype） 方法，该方法返回Blob， 文件夹＃createFile（blob） 方法，并尝试类似：
var gsBlob = excelfile.getAs（MimeType.GOOGLE_SHEETS）;
gsfolder.createFile（gsBlob）.setName（excelfile.getName（））;
然而，这也会返回一个错误：
不支持将application / vnd.openxmlformats-officedocument.spreadsheetml.sheet
转换为application / vnd.google-apps.spreadsheet。
查看 getAs 方法的文档表明，这通常是不受支持的操作，除非目标mimetype是 MimeType.PDF 。我的猜测是，这是因为PDF转换足够简单 - 它的实现可能使用类似打印的功能 - 而电子表格格式转换需要仔细处理公式，图像，图表等。
根据过去使用Google云端硬盘的经验，一般用户知道可以使用云端硬盘自动转换Excel - > Google表格。但是，此功能仅在上载期间可用。从密切相关的问题绘图，我们观察到我们必须使用Drive API高级服务，而不是简单的本地 DriveApp 。启用高级服务，然后可以使用以下代码段。请注意，高级云端硬盘服务将文件夹视为具有特定MIME类型的文件（这就是为什么没有文件夹特定的方法），因此同时使用DriveApp和高级服务对于Apps Script环境中的用户来说是最简单的。
//根据指定的目录将用户存储的Excel文件转换为Google电子表格。
//每天的最大转化次数都有配额限制：consumer @gmail = 250。
function convertExcelToGoogleSheets（）
{
var user = Session.getActiveUser（）; //用于所有权测试。
var origin = DriveApp.getFolderById（origin folder id）;
var dest = DriveApp.getFolderById（目标文件夹ID）;
//将拥有的Google表格文件的文件名索引为对象键（被散列）。
//这样可以避免需要搜索和执行多个字符串比较。
//每次迭代需要大约100-200毫秒来推进迭代器，检查文件$ b $ b //是否应该被缓存，并插入键值对。根据
//任务的大小，这可能需要单独完成，而是从存储设备加载。
//请注意，每秒查询的配额限制 - 每100秒1000个：
//如果顺序太大且循环太快，则需要Utilities.sleep（）用法。
var gsi = dest.getFilesByType（MimeType.GOOGLE_SHEETS），gsNames = {};
while（gsi.hasNext（））
{
var file = gsi.next（）;
if（file.getOwner（）。getEmail（）== user.getEmail（））
gsNames [file.getName（）] = true;
}
//查找并转换给定目录中未转换的.xls，.xlsx文件。
var exceltypes = [MimeType.MICROSOFT_EXCEL，MimeType.MICROSOFT_EXCEL_LEGACY];
for（var mt = 0; mt< exceltypes.length; ++ mt）
{
var efi = origin.getFilesByType（exceltypes [mt]）;
while（efi.hasNext（））
{
var file = efi.next（）;
//仅对没有拥有gs等价物的拥有文件执行转换。
//如果一个excel文件没有同名的gs文件，gsNames [...]将是未定义的，并且！undefined - > true
//如果一个excel文件有一个同名的gs文件，gsNames [...]将是true，并且！true - >如果（file.getOwner（）.getEmail（）== user.getEmail（）&&！gsNames [file.getName（）]）
{
Drive.Files。插入（
{title：file.getName（），parents：[{id：dest.getId（）}]}，
file.getBlob（），
{convert：true }
）;
//不要使用这个相同的名称转换更多电子表格。
gsNames [file.getName（）] = true;
code
我的上面的代码强制执行一个合理的要求，即您关心的文件是您拥有的文件。如果情况并非如此，则建议取消电子邮件支票。请注意，在特定的一天内转换太多电子表格。
如果使用应用程序脚本中的高级服务，因为没有特定的Apps Script文档等效内容，因此查看相关API的Google API API库文档通常会很有帮助。我个人发现 Python等效最容易使用。
I am working to automate a process. Currently, I am uploading Excel (.xlsx) files to a specific folder in Drive. I then manually convert them into a Google Sheet file, and move it into a separate folder. I would like to automate this process. I know I would have to write the script to iterate through the 2 folders to compare and see which files have yet to be converted, and then convert those it does not find in both locations. However, I am struggling with the best approach to make this happen.
The code below that may or may not be on the right track, I am rather new at this and really just tried piecing it together. Anyone's insight would be greatly appreciated.
function Excel2Sheets()
{
//Logs excel folder and string of files within
var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
var excelfiles = excelfolder.getFiles();
// Logs sheets folder and string of files within
var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
var ID = sheetfolder.getId();
var sheetfiles = sheetfolder.getFiles();
var MType = MimeType.GOOGLE_SHEETS;
while (excelfiles.hasNext()) {
var excelfile = excelfiles.next();
var excelname = excelfile.getName();
while (sheetfiles.hasNext()) {
var sheetfile = sheetfiles.next();
var sheetname = sheetfile.getName();
if(sheetname == excelname) {
break;
}
if(sheetfiles.hasNext(0)) {
var blob = excelfile.getBlob();
sheetfolder.createFile(excelname, blob, MType);
break;
}
}
}
}
I have also played around with this code. Thanks
function fileChecker()
{
try{
//Establishes Excel Source Folder
var excelfolder = DriveApp.getFolderById('1JbamZxNhAyZT3OifrIstZKyFF_d257mq');
//Establishes Sheet Target Folder
var sheetfolder = DriveApp.getFolderById('1y10IwMobCdpQlYwWdveHLzxEz3Xml0Qt');
//Establishes Return File Type
var MType = MimeType.GOOGLE_SHEETS;
//Gets all files in excel folder
var excelfiles = excelfolder.getFiles();
//loop through excel files
while(excelfiles.hasNext()){
//Establishes specific excel file
var excelfile = excelfiles.next();
//Checks for file with same name in sheets folder
var sheetfiles = sheetfolder.getFilesByName(excelfile.getName());
//Logical Test for file match
if(sheetfiles.hasNext()){
//Gets File Name
var excelname = excelfile.getName();
//Creates File Blob
var blob = excelfile.getBlob();
// Creates sheet file with given name and data of excel file
sheetfolder.createFile(excelname, blob, MType);
}
}
}
catch(err){
Logger.log(err.lineNumber + ' - ' + err);
}
}
解决方案 Either of your codes, if they were to reach the createFile line, should throw this error:
Invalid argument: file.contentType
because you are passing a Blob while createFile(name, content, mimetype) expects a String.
Reviewing the reference page for DriveApp, one will undoubtedly notice the File#getAs(mimetype) method, which returns Blob, and the Folder#createFile(blob) methods, and try something like:
var gsBlob = excelfile.getAs(MimeType.GOOGLE_SHEETS);
gsfolder.createFile(gsBlob).setName(excelfile.getName());
This too, however, will return an error:
Converting from application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
to application/vnd.google-apps.spreadsheet is not supported.
Looking at the documentation for the getAs method indicates that this is, in general, an unsupported operation unless the destination mimetype is MimeType.PDF. My guess is this is because the PDF conversion is simple enough - its implementation likely uses a "Print"-like functionality - while spreadsheet format conversion requires careful handling of formulas, images, charts, etc.
From past experiences with Google Drive, the general user knows that the ability to use Drive to perform automatic conversion of Excel -> Google Sheets exists. However, this functionality is only available during upload. Drawing from a closely related question, we observe that we must use the Drive API "Advanced Service", rather than the simpler, native DriveApp. Enable the Advanced Service, and then the following snippet can work. Note that the Advanced Drive Service treats folders as files having a specific mimetype (which is why there are no folder-specific methods), so using both DriveApp and the Advanced Service is easiest for those in the Apps Script environment.
// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets()
{
var user = Session.getActiveUser(); // Used for ownership testing.
var origin = DriveApp.getFolderById("origin folder id");
var dest = DriveApp.getFolderById("destination folder id");
// Index the filenames of owned Google Sheets files as object keys (which are hashed).
// This avoids needing to search and do multiple string comparisons.
// It takes around 100-200 ms per iteration to advance the iterator, check if the file
// should be cached, and insert the key-value pair. Depending on the magnitude of
// the task, this may need to be done separately, and loaded from a storage device instead.
// Note that there are quota limits on queries per second - 1000 per 100 sec:
// If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
while (gsi.hasNext())
{
var file = gsi.next();
if(file.getOwner().getEmail() == user.getEmail())
gsNames[file.getName()] = true;
}
// Find and convert any unconverted .xls, .xlsx files in the given directories.
var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
for(var mt = 0; mt < exceltypes.length; ++mt)
{
var efi = origin.getFilesByType(exceltypes[mt]);
while (efi.hasNext())
{
var file = efi.next();
// Perform conversions only for owned files that don't have owned gs equivalents.
// If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
// If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
{
Drive.Files.insert(
{title: file.getName(), parents: [{"id": dest.getId()}]},
file.getBlob(),
{convert: true}
);
// Do not convert any more spreadsheets with this same name.
gsNames[file.getName()] = true;
}
}
}
}
My code above enforces a somewhat-reasonable requirement that the files you care about are those that you own. If that's not the case, then removal of the email check is advised. Just beware of converting too many spreadsheets in a given day.
If working with the Advanced Service in Apps Script, it can often be helpful to review Google's API Client Library documentation for the associated API since there is no specific Apps Script documentation equivalent. I personally find the Python equivalent easiest to work with.
这篇关于Google Apps脚本创建excel文件的工作表版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
