0

I decided to open this topic because there is very little material in this regard and in my opinion many are asking the same question. I'm trying to write an script to convert an .xls or a .xlsm file stored on Google Drive to a Google Sheets file. I'm not interested in converting macros inside the .xlsm file, only converting the base file. This script should be able to overwrite the oldest file with the new one if it has the same name.

The topics in which I found more material are these:

  1. How to automatically import data from uploaded CSV or XLS file into Google Sheets
  2. Converting .xls to google spreadsheet in google apps script
  3. Convert all xls files available in a folder into "Google Doc Spreadsheets"?

these topic links inserted above are partial and in any case they do not allow the conversion of the .xlsm format.

I started from this code (taken from here), but I don't know how to modify it to make it convert both the .xls and .xlsm files and let them overwrite them if they are already in Google Drive

/**
* 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);
}

I would like to thank anyone who decided to help me and therefore to give all those who are asking the same question.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Giovanni
  • 3
  • 1
  • 5
  • Have you tried starting with your own code? Often this is more beneficial, as you can construct the layout in a way you understand. I would start by writing simple psuedocode for what you want to do. Rewrite it a few times, making it more step-by-step as you go. Then try to find the JavaScript or Apps Script methods that you need for each step (search their documentation websites for these). I would say your first task should be to *find* `.xlsm` files. Worry about converting after you can find them. – tehhowch Apr 15 '18 at 13:05
  • I've already posted the code I'm using with the comments for each call but I can not proceed because I do not know the tools to use. I was hoping that some programmer could help me. – Giovanni Apr 15 '18 at 13:39
  • 1
    this is not a tutorial site nor a forum. Your included code contains numerous errors, and calls methods for which you provide no definition. The "tools to use" are 0) the design/outline for your program that YOU create for it; 1) JavaScript language reference; 2) Apps Script language reference; and 3) the Apps Script editor's auto complete for methods and properties. – tehhowch Apr 15 '18 at 15:59
  • I think that making a question converting xls and xlsm is too broad as they are completely different formats. The first is binary format while the second is a zipped set of xml and other format files. – Rubén Apr 15 '18 at 16:48
  • Importing a csv is a simple matter. [Checkout Utilities.parseCSV()](https://developers.google.com/apps-script/reference/utilities/utilities#parseCsv(String,Char)). – Cooper Apr 15 '18 at 18:14
  • I do not understand why I'm treated like that. I can write simple scripts, I asked for support for a script that I can not write and I asked for help. Thanks anyway. – Giovanni Apr 15 '18 at 19:41
  • If you can write simple scripts, great. Please edit your question to include the script you have written to [find your desired files in Google Drive](https://developers.google.com/apps-script/reference/drive/drive-app), and the simple script you have written to convert that file to Google Sheets format, rather than the verbatim **test code** from the other question you linked. – tehhowch Apr 15 '18 at 20:30
  • Thanks Cooper! I did as you advised me, thank you – Giovanni Apr 17 '18 at 06:28

0 Answers0