CODEWITHSUNDEEP
excelgoogle-apps-scriptgoogle-sheetsoverwrite
Laura Premoli
Laura Premoli

Reputation: 13

Google Apps Script - convert xlsx to sheet overwriting the existing one

i'm facing a real big issue trying to convert an xlsx file to google sheet overwriting day by day the same file. Here below the code that generates every day a new file .sheet but I really need to overwrite the existing one in order to be able to update a google data studio dashboard with the updated data. Can you please help me?

function convertExceltoGoogleSpreadsheet(fileName) {
  try {

    fileName = fileName || "GDS_Stock_Positioning.xlsx";

    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get("1g5oIY5qeH5tNkMBGIzj1ddqX9DCXqJrz").parents[0].id;
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: "1g5oIY5qeH5tNkMBGIzj1ddqX9DCXqJrz"}],
    };

    Drive.Files.insert(resource, blob);
    Drive.Files.remove(fileId)

  } catch (f) {
    Logger.log(f.toString());
  }

}

function DeleteOldFiles() {
  var Folders = new Array(
    '1g5oIY5qeH5tNkMBGIzj1ddqX9DCXqJrz',
    '1g5oIY5qeH5tNkMBGIzj1ddqX9DCXqJrz'
  );
  var Files;

  Logger.clear();

  for (var key in Folders) {
    Folder = DriveApp.getFolderById(Folders[key])
    Files = Folder.getFiles();
    
    Logger.log('Opening Folder: ' + Folder.getName());

    while (Files.hasNext()) {
      var File = Files.next();

      if (new Date() - File.getLastUpdated() > 1 * 12 * 60 * 60 * 1000) {
        File.setTrashed(true); // Places the file in the Trash folder
        //Drive.Files.remove(File.getId()); // Permanently deletes the file
        Logger.log('File ' + File.getName() + ' was deleted.');
      }
    }
  }

  if(Logger.getLog() != '')
    MailApp.sendEmail('[email protected]', 'Backups have been removed from Google Drive', Logger.getLog());
}

Upvotes: 0

Views: 2120

Answers (1)

Tanaike
Tanaike

Reputation: 201428

I believe your goal as follows.

  • You want to overwrite the existing Google Spreadsheet using the EXCEL data.
  • You want to achieve this using Google Apps Script.

In this case, I think that this can be achieved by the method of "Files: update" of Drive API.

Sample script:

Before you use this script, please enable Drive API at Advanced Google services. And, please set the variables of xlsxFileName and spreadsheetId. xlsxFileName is the filename of XLSX file. spreadsheetId id the Spreadsheet ID of Spreadsheet which is overwritten by the XLSX data.

function myFunction() {
  const xlsxFileName = "###";  // Please set the filename of XLSX file.
  const spreadsheetId = "###";  // Please set the Spreadsheet ID. This Spreadsheet is overwritten by EXCEL data.

  const xlsx = DriveApp.getFilesByName(xlsxFileName || "GDS_Stock_Positioning.xlsx");
  if (!xlsx.hasNext()) throw new Error("No excel file.");
  Drive.Files.update({mimeType: MimeType.GOOGLE_SHEETS}, spreadsheetId, xlsx.next().getBlob());
}
  • When you run this script, the Spreadsheet of spreadsheetId is overwritten by the XLSX data of xlsxFileName or GDS_Stock_Positioning.xlsx. If xlsxFileName is not used, GDS_Stock_Positioning.xlsx is used. This is from your script.

IMPORTANT:

  • This script overwites the existing Spreadsheet. So please be careful. I would like to recommend to use a sample Spreadsheet for testing the script.

Reference:

Upvotes: 1

Related Questions