I have a google sheet that is update from a google Appsheet - works perfectly. Is there a method/script that I can add to the google sheet that would automatically update an excel sheet on the google drive or local hard automatically. (just one file/overwrite)

Any insight is appreciated Thank you

CodePudding user response：

Try

function exportSheetAsXLSXToDrive() {
  var id=SpreadsheetApp.getActiveSpreadsheet().getId()
  var name=SpreadsheetApp.getActiveSpreadsheet().getName()
  var d = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm")
  let blob = getFileAsBlob("https://docs.google.com/spreadsheets/d/" id "/export?format=xlsx&portrait=false&exportFormat=xlsx");
  let file = DriveApp.createFile(blob).setName(name '_' d '.xlsx');
  Logger.log(file.getUrl());
}
function getFileAsBlob(exportUrl) {
 let response = UrlFetchApp.fetch(exportUrl, {
     muteHttpExceptions: true,
     headers: {
       Authorization: 'Bearer '    ScriptApp.getOAuthToken(),
     },
   });
 return response.getBlob();
}

CodePudding user response：

About Is there a method/script that I can add to the google sheet that would automatically update an excel sheet on the google drive or local hard automatically., in the current stage, using Google Apps Script, there are no methods for directly achieving to overwrite the file on local PC. But, in the case of the file on Google Drive, you can overwrite the file.

In this answer, I would like to propose overwriting the file on Google Drive using Google Apps Script.

From your question, I believed your goal is as follows.

• You want to overwrite the existing XLSX file on Google Drive by converting Spreadsheet to XLSX data using Google Apps Script.

In this case, how about the following sample script?

Sample script:

This sample script uses Drive API. So, please enable Drive API at Advanced Google services. And, please set the variables of srcSpreadsheetId and xlsxFileId on Google Drive. If you want to use the active Spreadsheet, you can use const srcSpreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId().

function myFunction() {
  const srcSpreadsheetId = "###"; // Please set the source Spreadsheet ID.
  const xlsxFileId = "###"; // Please set the XLSX file ID.

  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id="   srcSpreadsheetId;
  const res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } });
  const blob = res.getBlob();
  if (Drive.Files.get(xlsxFileId).id) {
    const obj = Drive.Files.update({}, xlsxFileId, blob);
    console.log(obj.id); // Here, you can see that the XLSX file ID is not changed.
  } else {
    // If your "xlsxFileId" is not found, the converted XLSX data is created as a new XLSX file.
    const file = DriveApp.createFile(blob);
    console.log(file.getId()); // Please set this file ID to "xlsxFileId". By this, from the next run, the XLSX file of the inputted ID is overwritten.
  }
}

• When this script is run, when the XLSX file of xlsxFileId is existing, the XLSX file is overwritten by the Spreadsheet of srcSpreadsheetId.
• When this script is run, when the XLSX file of xlsxFileId is not existing, a new XLSX file is created.
  • In this case, as a sample, a new XLSX file is created to the root folder on Google Drive.
  • When a new XLSX file is created, you can see the file ID in the log. Please set this ID to the variable of xlsxFileId. By this, from the next run, the XLSX file of the inputted ID is overwritten.

Reference:

• Files: update