Convert XLS files to Google Spreadsheet using Google Apps Script
My name is Alexander and I am a freelancer, my main specialization is Google Apps Script. One of the customers needed to programmatically convert Microsoft Excel files to Google Spreadsheets. I had never encountered such a task before, so I called Google for help. I rummaged through a bunch of forums, but did not find a ready-made solution. I had to write my bike. And although the code was short, it took quite a bit of time to find a solution. Here's what happened:
This function takes as a parameter the ID of the folder in which the files that need to be converted are located. Finds files in this folder that correspond to the MimeType.MICROSOFT_EXCEL_LEGACY type, these are Microsoft Excel XLS documents, and make copies of them in the same folder. When copying, the format is converted, the convert: true parameter is responsible for this. If the .xls is in the name of the original file, it is deleted.
If you need to process XLSX files, then change the type to MimeType.MICROSOFT_EXCEL and
But if you just copy this code and try to run it, it will give an error "ReferenceError: The“ Drive ”object is not defined." To fix this, you need to select the “Additional Google features ...” item in the Resources menu. In the window that appears, find the “Drive API” and enable it.
')
Then, in the “Resources” menu, select the “Developers Console Project ...” item. In the window that appears, click on the link with the project ID.
On the page that appears, in the left menu, select "APIs & auth" then "APIs". A list of API will appear, in which we follow the link “Drive API”.
Click on the button “Enable API” - that's it. You can run.
Similarly, you can convert files of other formats.
I hope that this note will save someone time.
function convert(folderId) { var folderIncoming = DriveApp.getFolderById(folderId); var files = folderIncoming.getFilesByType(MimeType.MICROSOFT_EXCEL_LEGACY); while (files.hasNext()) { var source = files.next(); var sourceId = source.getId(); var fileName = source.getName().replace('.xls', ''); var file = { title: fileName, }; file = Drive.Files.copy(file, sourceId, {convert: true}); } } This function takes as a parameter the ID of the folder in which the files that need to be converted are located. Finds files in this folder that correspond to the MimeType.MICROSOFT_EXCEL_LEGACY type, these are Microsoft Excel XLS documents, and make copies of them in the same folder. When copying, the format is converted, the convert: true parameter is responsible for this. If the .xls is in the name of the original file, it is deleted.
If you need to process XLSX files, then change the type to MimeType.MICROSOFT_EXCEL and
var fileName = source.getName().replace('.xls', ''); var fileName = source.getName().replace('.xlsx', ''); But if you just copy this code and try to run it, it will give an error "ReferenceError: The“ Drive ”object is not defined." To fix this, you need to select the “Additional Google features ...” item in the Resources menu. In the window that appears, find the “Drive API” and enable it.
')
Then, in the “Resources” menu, select the “Developers Console Project ...” item. In the window that appears, click on the link with the project ID.
On the page that appears, in the left menu, select "APIs & auth" then "APIs". A list of API will appear, in which we follow the link “Drive API”.
Click on the button “Enable API” - that's it. You can run.
Similarly, you can convert files of other formats.
I hope that this note will save someone time.
Source: https://habr.com/ru/post/428616/
All Articles