CODEWITHSUNDEEP
excelgoogle-apps-script
user1781336
user1781336

Reputation: 85

How do I copy data from an xlsx file in my google drive to a google sheet?

I have an excel file (.xlsx) saved on my google drive. I want to copy the data from there into a tab in a google sheet file i have already created. I have the following code that runs. But I don't know exactly how to specify the Google Sheet (and tab) to paste the excel data in?

``

function run() {
  try {

    fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.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());
  }

}

Upvotes: 1

Views: 2530

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to copy the values of ".xlsx" file to the existing Google Spreadsheet.
  • There is the ".xlsx" file in your Google Drive.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • In order to retrieve the values from the Google Spreadsheet converted from the ".xlsx" file, it uses the file ID returned from Drive.Files.insert().
  • In your script, it is required to declare the destination Google Spreadsheet which copies the values from the ".xlsx" file and to use it.

Modified script:

When your script is modified, please modify as follows. Before you run the script, please set the variables of destSpreadsheetId and destSheetName.

From: 
fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.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
});
To: 
var destSpreadsheetId = "###";  // Added
var destSheetName = "###";  // Added

fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.xlsx";
var excelFile = DriveApp.getFilesByName(fileName).next();
var fileId = excelFile.getId();
// var folderId = Drive.Files.get(fileId).parents[0].id; // This is not used in your script.
var blob = excelFile.getBlob();
var resource = {title: excelFile.getName().replace(/.xlsx?/, "")};  // Modified
var sourceSpreadsheet = Drive.Files.insert(resource, blob, {convert: true});  // Modified

// Also I added below script.
var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheet.id).getSheets()[0];
var destSheet = SpreadsheetApp.openById(destSpreadsheetId).getSheetByName(destSheetName);
var values = sourceSheet.getDataRange().getValues();
destSheet.getRange(destSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

Note:

  • In your question, I'm not sure whether the ".xlsx" file has several sheets and the range you want to put the values. So in this modification, as a sample, the values of the 1st tab in the converted Spreadsheet are copied to the last row in the destination Spreadsheet.
  • In this modified script, it supposes that you have already enabled Drive API at Advanced Google services.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 5

Related Questions