Google Apps Script creates sheets version of excel file

Question

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

Answer 1

Sub EnhanceSpreadsheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name

    ' Step 1: Add Descriptive Column Headers
    ws.Cells(1, 1).Value = "ID or Base Value"
    ws.Cells(1, 2).Value = "Relevant Label" ' Adjust as necessary
    ws.Cells(1, 3).Value = "Step Index"
    ws.Cells(1, 4).Value = "Fine Levels"
    ws.Cells(1, 5).Value = "Major Levels"

    ' Step 2: Introduce Calculated Columns
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Add Percentage Change Column
    ws.Cells(1, 6).Value = "Percentage Change"
    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i - 1, 4).Value <> 0 Then
            ws.Cells(i, 6).Formula = "=(" & ws.Cells(i, 4).Address & "-" & ws.Cells(i - 1, 4).Address & ")/" & ws.Cells(i - 1, 4).Address & "*100"
        End If
    Next i

    ' Add Cumulative Change Column
    ws.Cells(1, 7).Value = "Cumulative Change"
    ws.Cells(2, 7).Formula = "=" & ws.Cells(2, 4).Address & "-" & ws.Cells(2, 1).Address
    For i = 3 To lastRow
        ws.Cells(i, 7).Formula = "=" & ws.Cells(i, 4).Address & "-" & ws.Cells(2, 1).Address
    Next i

    ' Step 3: Include Metadata or Context
    ws.Cells(1, 8).Value = "Timestamp"
    ws.Cells(1, 9).Value = "Market Condition"
    ws.Cells(1, 10).Value = "Instrument Identifier"

    ' Step 4: Visual Representation
    Dim chartObj As ChartObject
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
    With chartObj.Chart
        .SetSourceData Source:=ws.Range("D1:E" & lastRow) ' Fine Levels and Major Levels
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Text = "Progression of Fine and Major Levels"
    End With
End Sub

Answer 2

This works for me when saving from a Gmail attachment blob of an Excel file to a Sheet without Advanced Services.

function saveExcel(blob, filename){
    let resources = { title: filename };
    let options = { convert: true };
    let file = Drive.Files.insert(resources, blob, options);
    let fileId = file.getId();
    return fileId;
}

If I'm reading files from a Drive folder, I use the following, for example:

function readExcelFiles() {
    let files = convertFolder.getFiles();
    let filesFound = 0;
    let options = { convert: true };
    while (files.hasNext()) {
        let blob = file.getBlob();
        let blobType = blob.getContentType();
        let isXls = ((blobType.indexOf("spreadsheet") > -1) || (blobType.indexOf("ms-excel") > -1));
        if (isXls) {
            filesFound += 1;
            let resources = { title: "tmp" + filesFound };
            let file = Drive.Files.insert(resources, blob, options);
        }
    }
}

One thing I've found that can be important is using unique filenames. After being saved to Drive as a converted file, they are Google Sheets and can be read with SpreadsheetApp.

Answer 3

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.