0

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;
}
Alenros
  • 816
  • 7
  • 23
FiddlerOfAnyBytes
  • 3
  • 1
  • 1
    In your goal, you want to overwrite the existing whole Spreadsheet with XLSX data without changing the file ID of Spreadsheet. Is my understanding correct? – Tanaike Jan 25 '21 at 01:12
  • To keep this simple, yes. Some would append, some would clear contents & write, but all that happens outside this function. – FiddlerOfAnyBytes Jan 25 '21 at 22:54
  • Thank you for replying. From your replying, I proposed a sample script for achieving it. Could you please confirm it? If I misunderstood your goal, I apologize. By the way, in that sample script, the existing Spreadsheet is overwritten. Please be careful this. So when you test this script, I would like to recommend to use a sample Spreadsheet. – Tanaike Jan 26 '21 at 01:02
  • I should have probably altered the script. I will likely alter the blob to be a parameter and append to it before it enters this function. So if it needs appended or overwritten will not matter for this function as it would already be altered to always overwrite. – FiddlerOfAnyBytes Jan 26 '21 at 14:19
  • Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot understand about your replying. Can I ask you about the detail of your goal? I proposed the following answer from your `To keep this simple, yes.`. So if I misunderstood your question, I apologize. At that time, can you provide the detail of your goal? From this, I would like to modify my answer. – Tanaike Jan 27 '21 at 00:29
  • That's okay, I was trying to simplify the issue as much as possible. From your answer, I had tested it and it worked. Sense then I have expanded without issues. I had tried using Drive.Files.update() previously but didn't have the parameters set correctly. Seeing your example cleared up my misunderstanding perfectly. Thank you! – FiddlerOfAnyBytes Feb 05 '21 at 21:39

1 Answers1

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • how could I use this with a local Excel file? – ryanbuckner Aug 16 '22 at 01:15
  • @ryanbuckner I would like to support you. But, unfortunately, this is not your question. So, can you post it as a new question by including more information? Because, about your question of `how could I use this with a local Excel file?`, in your question, I cannot imagine it. I apologize for this. By this, it will help users including me think of a solution. If you can cooperate to resolve your question, I'm glad. Can you cooperate to do it? – Tanaike Aug 16 '22 at 01:19