0

I am beginner in Google script ,So apologies if i asked any silly things I need to import the Excel file (which i received in gmail) in to existing Google sheet. The below code will do the same thing which i req But the Below code import the CSV only , Can any one help me to modify this so that the code will import the Excel file .

function importCSVFromGmail() {
  var threads = GmailApp.search("from:reports@example.com");var message = threads[0].getMessages()[0];
  var attachment = message.getAttachments()[0];
  if (attachment.getContentType() === "text/csv") { 
    var sheet = SpreadsheetApp.getActiveSheet();
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");
    sheet.clearContents().clearFormats();
    sheet.getRange(1, 1,csvData.length,csvData[0].length).setValues(csvData);
 }
}
iJay
  • 4,205
  • 5
  • 35
  • 63
Abhishek Soni
  • 11
  • 1
  • 2
  • Hi Nico, Actually i am not able to add code. I am using the below code , Please use the link (I am hearty apologies ) https://ctrlq.org/code/20279-import-csv-into-google-spreadsheet This code work for CSV file but i my case i have .xlsx file as source – Abhishek Soni Jul 23 '18 at 09:06
  • Why can't you edit the question and add the code you've announced? Using an external link only, this question gets useless if the linked content changes or vanishes – Nico Haase Jul 23 '18 at 09:08
  • Sorry , Now i have added the code, the code works good if i import csv but in my case, Its excel file as source – Abhishek Soni Jul 23 '18 at 09:20

1 Answers1

0

In your code this line var csvData = Utilities.parseCsv(attachment.getDataAsString(), ","); does all heavylifting of converting csv file data into a 2D array, which you're dumping into google sheet.

I think you can do something similar using some library to parse excel files.

Have a look here http://codetheory.in/parse-read-excel-files-xls-xlsx-javascript/

So the code will become something like this :

// Search for attachment

// Check for it's content-type

// If it's a csv file, process it and convert into a 2D array

// Else if it's a excel file then use some library and convert it into 2D array

// Store that 2D array in sheet
Umair Mohammad
  • 4,489
  • 2
  • 20
  • 34
  • Yes you are correct but in Google script i am not able to find such parsing – Abhishek Soni Jul 23 '18 at 10:45
  • Yes, maybe you'll need to integrate some 3rd party library for that. did you checked that link in my answer? check out this also http://oss.sheetjs.com/js-xlsx/ – Umair Mohammad Jul 23 '18 at 11:30
  • Dear all , Sorry But is i send my query in correct group Google Script – Abhishek Soni Jul 23 '18 at 12:32
  • 2
    Why use a third party? Google can import Excel files and convert them to Google Sheets files. Then op simply needs to use `SpreadsheetApp` methods to read the new Drive file and write the desired data. – tehhowch Jul 23 '18 at 14:22
  • Thanks Can you please elaborate a bit or if you have code then it would be much appreciated Its bit urgent for me – Abhishek Soni Jul 23 '18 at 14:44
  • https://stackoverflow.com/questions/26854563/how-to-automatically-import-data-from-uploaded-csv-or-xls-file-into-google-sheet – Umair Mohammad Jul 24 '18 at 05:20
  • @tehhowch do you mean something like this https://stackoverflow.com/questions/26854563/how-to-automatically-import-data-from-uploaded-csv-or-xls-file-into-google-sheet? – Umair Mohammad Jul 24 '18 at 05:21
  • Thanks guys so the belwo is the process to import excel into google sheet .via google sript I have move the gmail excle file.into google drive then from drive to.google sheet. Successfully .now the final issue is coping the google sheet data into target sheet. The easiest way it copy data via file id but I coild not find any function which gives me file id via file name .please help me – Abhishek Soni Jul 25 '18 at 05:54