How do you Export to Google Sheets?

Ignition
1

I'm not sure how doable this is but I figured this is probably a good place to ask.

I know how to create an Excel file in Ignition. Is there a way to send that to Google sheets programatically?

I did a quick look and it looks like the Google sheets API requires Python 3.10 or higher which I understand to not be supported by Ignition's Jython but I figure there's probably a way to do this and someone can point me in the right direction.

2

Ok, you nerd-sniped me. :nerd_face:

If you are willing to add Google AppScripts to your repertoire:

function main() {
  var size, files, folderID;
  folderID = "aaa"; // Use your folder ID here.

  [size, files] = getFiles(folderID);
  
  if (size > 0){
    files.forEach(function(file){
      // Get the Excel file object.
      excelFile = DriveApp.getFileById(file['id']);
      
      // Set up a new name without the .xlsx extension
      name = String(file['name']).replace('.xlsx', '');
      
      excelBlob = excelFile.getBlob();
      
      newFile = {
        title : name,
        parents: [{id: folderID}]
      };
      // Create Google Sheet
      Drive.Files.insert(newFile, excelBlob, {
        convert: true
      });
      // Delete Excel file
      Drive.Files.remove(file['id']); // If this line is run, the original XLSX file is removed. So please be careful this.

      Logger.log('File ' + name + ' processed')
    });
  };
};


function getFiles(folderID){
  var folderID, arrayFiles, filesByType
  // Get the folder object
  folder = DriveApp.getFolderById(folderID);  
  
  arrayFiles = [];
  
  //Get files by Excel MimeType 
  filesByType = folder.getFilesByType('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  while (filesByType.hasNext()) {//If no files are found then this won't loop
      file = filesByType.next();
      // Add to the list-- erm, array-- of files to process
      arrayFiles.push({'id':file.getId(), 'name':file.getName()});
    }

  return [arrayFiles.length, arrayFiles];
};

I personally use rclone to either create a sync job, or a mount point for Drive that's available to the gateway.

The AppScript can be triggered to automatically process the files, although Google won't guarantee precise timing.

image
image703×780 34.8 KB

5 Likes
3

A novel, pragmatic solution.

In theory, the "right" way to do this would probably be an HTTP request that directly uploads the Excel file to Google, and let Sheets handle the opening as XLSX -> converting to Google format automagically. But from past experience, implementing Google's web API standards "raw" (outside of an SDK) is a painful, uphill slog.

1 Like
4

Thanks for your responses. I'll check it out.