How to get the excel data from a url into google sheets using google apps script?

Question

Follow up question to this question - How to fix invalid url error using UrlfetchApp?

This is the url to download the excel data from : https://corvo-reports.s3.amazonaws.com/TRESAH/2020-08-16/45d32ff8-bccd-4c16-8916-6c19c28f2f3c%402020-08-16%2017%3A30%3A00.0/Sponsored%20Products%20Search%20term%20report%20-%20Scotch%20Brite.xlsx?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20200816T174421Z&X-Amz-SignedHeaders=host&X-Amz-Expires=604799&X-Amz-Credential=AKIAY2R3XYZC46Q4PK5E%2F20200816%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Signature=726ca9b98cd766966b756bc708b780377ff62e5bbfec8c09b97294bfb0cd63f7

This is a report that comes in on a schedule and I need to take this data and put it in a google sheet. It's a large report so I need to clear all existing data on the sheet and replace it with the new data. I've been struggling with this and could use some help.

This is the code I have right now:

// Globals
const DataFolderId = 'XXXXX'
const label = 'YYYYY'

/*
* Download the excel file and put it in a google sheet
*
* @return : Hopefully, it adds the data to the google sheet
*/
function excelToSheets() {    
  
  // Get the link to the excel file
  const url = getDownloadLink(label) 
  const excelFile = UrlFetchApp.fetch(url).getBlob()
  const filename = 'Report: ' + Utilities.formatDate(new Date(), "IST", "yyyy-MM-dd")
  
  // Download the file to google drive
  const fileInfo = {
  title: filename,
  mimeType: "MICROSOFT_EXCEL",
  "parents": [{'id': DataFolderId}],
}  

// Currently, this adds a zip file to google drive, instead of an excel/google sheet file
const file = Drive.Files.insert(fileInfo, excelFile, {convert: true})  

// Assign the id of the downloaded file to a variable
const id = file.id

// Put data in a spreadsheet - this does not work
spreadsheet(id)

}


/*
* Helper function to put data in the google sheet
*/
function spreadsheet (id) {
  const source = SpreadsheetApp.openById(id)
  const destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data')
  destination.clearContents()
  source.copyTo(destination)  
}


/*
* Get the download link from the gmail label
*
* @return : The link from the most recent email
*/
function getDownloadLink (label) {
  
  const lookupLabel = GmailApp.getUserLabelByName(label)
  const thread = lookupLabel.getThreads()[0]  
  const message = thread.getMessages()[0]  
  const data = message.getPlainBody()
  const regExp = new RegExp('[\n\r].*Download:\\s*([^\n\r]*)')
  const link = regExp.exec(data)[1].trim()
  
  return link 
}

Currently, the excelToSheets() function takes the excel file from the link and adds a zip file in google drive. I'm not sure what I'm doing wrong here - I've just been trying to follow a bunch of tutorials on the topic online.

Your help would be greatly appreciated!

Answer 1

When you fetch a blob from a download link it might not contain the correct mimeType information

Workaround

Perform the request in two steps

1. step save the file as Excel on your Drive
2. Convert the Excel file to Google Sheets

Sample

function excelToSheets() {    
  
  const url = getDownloadLink(label); 
  const excelFile = UrlFetchApp.fetch(url).getBlob();
  const filename = 'Report: ' + Utilities.formatDate(new Date(), "IST", "yyyy-MM-dd")
  const fileInfo = {
    title: filename,
    mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    parents: [{id: DataFolderId}],
  }  
  
  const file = Drive.Files.insert(fileInfo, excelFile)  
  const id = file.id;
  const excelFile2 = DriveApp.getFileById(id).getBlob();
  const fileInfo2 = {
    title: filename,
    mimeType: MimeType.GOOGLE_SHEETS,
    parents: [{id: DataFolderId}],
  }  
  const file2 = Drive.Files.insert(fileInfo2, excelFile2); 
  const id2 = file2.id;
  spreadsheet(id2)
  
}