CODEWITHSUNDEEP
google-apps-script
MrPea
MrPea

Reputation: 655

Can I overwrite the contents of an Excel file (exported from Google Sheets) saved on Google Drive with apps script

I use Google Apps script to export a Google Sheet as an Excel file to Google Drive. The Excel file then syncs with 6 others users to their local machines via the Google Drive Desktop app. Each time the Google Sheet is exported it creates a new file rather than replacing the old one albeit with the same filename and deletes the original version. This would be fine except that when it then syncs, with Google Drive Desktop, Windows deletes the original version and sends it to the recycle bin.

The file is created every 15 minutes and is 3mb in size so after a few weeks the recycle bins are full of Gigabytes of data.

Is it possible to update the contents of a file rather than create a new file?

Here's what I use at the moment:

      var blob = exportAsExcel(spreadsheetId)
      var file = DriveApp.createFile(blob).setName(excelFileName);
      fileId = file.getId();
      file.makeCopy(destinationFolder);
      Drive.Files.remove(fileId);
   }


    function exportAsExcel(spreadsheetId) {
    var file = Drive.Files.get(spreadsheetId);   
      var url = file.exportLinks['application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet'];
      var token = ScriptApp.getOAuthToken();
      var response = UrlFetchApp.fetch(url, {
        headers: {
          'Authorization': 'Bearer ' +  token
        }
      });
      return response.getBlob();
}

Upvotes: 0

Views: 382

Answers (1)

Tanaike
Tanaike

Reputation: 201398

You can overwrite a file using Drive API. The detail information is https://developers.google.com/drive/v3/reference/files/update.

I prepared a method to overwrite a spreadsheet to existing excel file. This method uses your method exportAsExcel(). At first, please confirm whether Drive API is enabled at Google API console and Advanced Google services.

src_sheetId and dst_fileId are spreadsheet ID and existing excel file that you want to overwrite, respectively. By running overWrite(), the existing excel file is overwritten. So the file name and file ID of excel file are not changed.

Script :

function overWrite(src_sheetId, dst_fileId) {
  UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/drive/v3/files/" + dst_fileId + "?uploadType=multipart",
    {
      method: "PATCH",
      headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      payload: exportAsExcel(src_sheetId).getBytes()
    }
  );
}

By the way, does exportAsExcel() of your script work fine? When I use it, an error occurs. I removed the error by modification below.

From :

var url = file.exportLinks['application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet'];

To :

var url = file.exportLinks['application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];

If I misunderstand your question, I'm sorry.

Upvotes: 2

Related Questions