CODEWITHSUNDEEP
google-apps-scriptgoogle-apps-script-api
FiddlerOfAnyBytes
FiddlerOfAnyBytes

Reputation: 3

How to use Advanced Drive Services properly to replace an existing spreadsheet instead of making a new one every time the function gets called?

I have been taking a bunch of source spreadsheets from a shared corporate drive, normalizing them, and combining them into a master sheet for my uses. Some of the sheets get uploaded as excel docs. Its impractical to expect the typical employee to get the formatting right or to have someone manually alter the files so I wrote a script to do it.

The only issue in the entire process is that every time someone uploads, updates or replaces an excel file, the code has to make a new temporary copy in Google Sheets format instead of overwriting an existing one. Additionally, I have to call a deleteTempFile(tempID) function a variety of spots (trying to avoid using globals) based on how the temp file needs altered before it gets piped to the master file.

I've tinkered with the following code for a few weeks now but always go back to this as I'm just not finding anyway that actually works to use Drive.Files.insert on existing Sheets or anything useful on Drive.Files.update.

function convertXlsxToGsheets(excelFile, tempName, tempFolderID) {
  var blob = excelFile.getBlob();

  var payload = {title: tempName, parents: [{id:tempFolderID}]};  // Problem lines
  var tempSpreadsheet = Drive.Files.insert(payload, blob, {convert: true});  // Problem lines

  var tempID = tempSpreadsheet.getId();
  Logger.log('Temporary Spreadsheet ID: ' + tempID);
  return tempID;
}

Upvotes: 0

Views: 121

Answers (1)

Tanaike
Tanaike

Reputation: 201683

I believe your goal as follows.

  • You want to overwrite the existing whole Spreadsheet with XLSX data without changing the file ID of Spreadsheet.
  • You want to achieve this using Drive API of Advanced Google services.

In this case, I think that Drive.Files.update() of Drive API is used. The sample script is as follows.

Sample script:

Before you use this script, please confirm whether Drive API has already been enabled at Advanced Google services. Ref

var existingSpreadsheetId = "###";  // Please set the Spreadsheet ID of the existing spreadsheet you want to overwrite.
var blob = excelFile.getBlob();  // This is from your script.
Drive.Files.update({}, existingSpreadsheetId, blob);

IMPORTANT:

In this sample script, the existing Spreadsheet is overwritten. Please be careful this. So when you test this script, I would like to propose to use a sample Spreadsheet.

Reference:

Upvotes: 1

Related Questions