Script to convert Excel to Spreadsheets including overwrite?

[Marco Nieuwenhuis]

Hi Experts,

I'm new/not familiar with app scripting language. I need a script (or part off) that converts a named Excel file to Spreadsheet format in a specific Drive folder. I found a code that does the converting part but every time when I execute the script it generates a new Spreadsheet. What I need is a code/script that does the same but overwrites the 'main' Spreadsheet which is shared and used for Data Studio as data source. 

Hope you Experts have a solution to do this. Below the code I use for the converting part.

Thanks in advance!

Best Regards,

Marco Nieuwenhuis

Used script that generates a new Spreadsheet =

function convertExceltoGoogleSpreadsheet2(fileName) {

  

  try {

    

    fileName = fileName || "test_ds.xlsx";

    

    var excelFile = DriveApp.getFilesByName(fileName).next();

    var fileId = excelFile.getId();

    var folderId = Drive.Files.get(fileId).parents[0].id;  

    var blob = excelFile.getBlob();

    var resource = {

      title: excelFile.getName().replace(/.xlsx?/, ""),

      key: fileId

    };

    Drive.Files.insert(resource, blob, {

      convert: true

    });

    

  } catch (f) {

    Logger.log(f.toString());

  }

  

}

[Kanshi Tanaike]

- You want to overwrite the existing Spreadsheet by a Excel file.

If my understanding is correct, how about using the method of files.update in Drive API? The sample script is as follows. You can use this method using Advanced Google Services.

## Sample script:

var dstFileId = "###"; // file ID of Existing Spreadsheet

var srcFileId = "###"; // file ID of Excel file

Drive.Files.update({}, dstFileId, DriveApp.getFileById(srcFileId));

## Note:

- This is a simple sample script. So please modify it for your situation.

- When you run this script, the existing Spreadsheet is overwritten. Please be careful this. So I recommend that at first, please test it using a sample Spreadsheet.

[Marco Nieuwenhuis]

Hi Kanshi,

That's correct. The Excel is pushed to Drive via Backup and Sync.

If the Excel keeps the same ID it should work.

I will test it out and let you know.

Thanks!

Best Regards, 

Marco

On Wed, Mar 27, 2019 at 9:46 AM Kanshi Tanaike <kanshi...@gmail.com> wrote:

- You want to overwrite the existing Spreadsheet by a Excel file.

If my understanding is correct, how about using the method of files.update in Drive API? The sample script is as follows. You can use this method using Advanced Google Services.

## Sample script:

var dstFileId = "###"; // file Id of Existing Spreadsheet

var srcFileId = "###"; // file Id of Excel file

Drive.Files.update({}, dstFileId, DriveApp.getFileById(srcFileId));

## Note:

- This is a simple sample script. So please modify it for your situation.

- When you run this script, the existing Spreadsheet is overwritten. Please be careful this. So I recommend that at first, please test it using a sample Spreadsheet.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/53752c94-97c1-4764-9371-ddc6f5456eb4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marco Nieuwenhuis]

Hi Kanshi,

For testing I uploaded the Excel to Drive. Then I open as Spreadsheet so I have a Spreadsheet copy.

The ID of the Spreadsheet is in the URL correct(?) but where can I retrieve/find the Excel file ID? Can't find it....maybe not looking correct...

Thanks in advance for your answer.

Best Regards,

Marco Nieuwenhuis 

[Marco Nieuwenhuis]

Hi Kanshi,

it's working now. Used the script code below. Thanks for your help!

var dstFileId = 'id_off_spreadsheet'; // file Id of Existing Spreadsheet

function convertExceltoGoogleSpreadsheet(fileName) {

fileName = fileName || "excel_file_name.xlsx"; //  excel_file_name.xlsx = name of specific Excel file

var excelFile = DriveApp.getFilesByName(fileName).next();

var fileId = excelFile.getId();

var srcFileId = fileId; // file Id of Excel file

Drive.Files.update({}, dstFileId, DriveApp.getFileById(srcFileId))};

Best Regards,

Marco