google-sheetsgoogle-apps-scriptxlsx

Writing to a cell in a XLSX file using Sheets Script


I'm using Google Sheets Script and would like to write data to a XLSX but preserve the XLSX file format so other applications can read it. I have tried the following without success. When I open the file with Sheets everything looks good but the file no longer works in Excel or other applications that read Excel files.

function LabelOutput(){
  var select1;
  var selectCount;
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeRange = activeSheet.getActiveRange();
  var select1 = activeRange.getRow();
  var selectCount = activeRange.getNumRows();
  var TL = "TL";
  var Loc = "Location";
  var Shape = "Shape";
  var shpColor = "Color";
  var prgName = "Program";
  var Notes = "Notes";
  var outputData = TL + ", " + Loc+ ", " + Shape+ ", " + shpColor + ", " + prgName + ", " + Notes;

  var i;
  var j = selectCount + select1 - 1;

  for (i = select1; i <= j; i = i + 1) {                                       //add selected data
    if(activeSheet.isRowHiddenByFilter(i) == false){
      TL = activeSheet.getRange('T' + i).getValue();
      Loc = activeSheet.getRange('U' + i).getValue();
      Shape = activeSheet.getRange('AN' + i).getValue();
      Shape = Shape.substring(0,1);
      if(Shape == ","){
        Shape = '","';
      }
      shpColor = activeSheet.getRange('AP' + i).getValue();
      prgName = activeSheet.getRange('AM' + i).getValue();
      Notes = activeSheet.getRange('E' + i).getValue();
      outputData += "\n" + TL + ", " + Loc+ ", " + Shape + ", " + shpColor + ", " + prgName + ", " + Notes;
    }
  }
  var excelFile = DriveApp.getFileById('1--S0-GLPwVqAj94vbPFxfzGrIT-WOGwz');
  excelFile.setContent(outputData);
}

I've also tried to treat the XLSX file like a SpreadSheetApp but it gives a type error.

var testingOutput = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Label Print.XLSX');
testingOutput.getRange("A2").setValue("testing");

I also tried to do something like this and it worked but it keeps making new copies rather than writing over existing copies. Export a Google Sheet to Google Drive in Excel format with Apps Script

function convertSheetToXLSX() {
  var sheetId = "2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c";
  var spreadsheetName = "My Spreadsheet";
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");
  var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetId + "&exportFormat=xlsx";  
  var params = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  blob.setName(spreadsheetName + ".xlsx");
  destination.createFile(blob);
}

Solution

  • SUGGESTION

    You can modify the second script to delete the old file that's been created to stop it from making new copies in your Google Drive folder and so that it would look like you're writing over existing copies.

    You can achieve that by adding the following to it:

      var getFile = destination.getFilesByName(`${spreadsheetName}.xlsx`);
      var file = getFile.hasNext() ? getFile.next() : null;
      file ? file.setTrashed(true) : null;
    

    The full code would become:

    function convertSheetToXLSX() {
      var sheetId = "2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c";
      var spreadsheetName = "My Spreadsheet";
      var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");
      var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetId + "&exportFormat=xlsx";
      var params = {
        method: "get",
        headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
        muteHttpExceptions: true
      };
      var blob = UrlFetchApp.fetch(url, params).getBlob();
      var getFile = destination.getFilesByName(`${spreadsheetName}.xlsx`);
      var file = getFile.hasNext() ? getFile.next() : null;
      file ? file.setTrashed(true) : null;
      blob.setName(spreadsheetName + ".xlsx");
      destination.createFile(blob);
    }
    

    This uses getFilesByName(name) to check if the file with the same name is already in the folder. If there is, it'll delete the file and create a new one, and if there's none, it creates the file.