Q: The OP asks: is it possible to transfer an Excel spreadsheet, received as an Outlook attachment, to google-drive, and then update it to a Google Sheet format?
A: Yes.
Q: What are the discrete steps required to satisfy the OP's objective?
A:
1 - Write a macro in Outlook to automatically forward the email and Excel attachment to the OP's GMail account; specifically to the account used to log into Google Sheets. For the purposes of this answer this aspect, though fairly straightforward, this is treated as off topic.
2 - Create a folder (or folder/subfolder) in Google Drive where the Excel spreadsheet can be saved. This could be done manually or by script. "Manually" is easier, "by script" is more fun and offers more flexibility.
3 - Run a Google script to access the OP's Gmail account, identify Excel attachments (say, files ending with 'xls', 'xlsx', 'xlsm' - other variations can be added), and save the Excel file to the Google Drive folder. Attach a "label" to each relevant email so that only new unprocessed messages are processed.
4 - Run a Google script to access the Google Drive folder/subfolder and convert the Excel spreadsheet to a Google Sheet.
As noted in comments, this topic has been raised, in one form or another, many times before:
Is there any google API which could save gmail message attachments to google drive? and
Convert all xls files available in a folder into “Google Doc Spreadsheets”? are two good examples. Google search will reveal many other topics - on StackOverflow and other sources.
However Google services (Docs, scripts, APIs, etc) are being constantly enhanced. One of the by-products of this development is that some methods are discontinued and this can render a previously correct answer to become out-of-date.
Unfortunately this is the case with the excellent GmailToDrive() function supplied in the Jan 2018 answer to Is there any google API which could save gmail message attachments to google drive?. Google Advanced Drive Service and Drive API changed during 2018 and that excellent answer now falls at the last hurdle.
The following code is supplied in order to provide an update to February 2019.
The basis of this answer is the GmailToDrive() function provided by Mr.Rebot, but modified for Excel files.
Trigger
The code should be set as a time-driven, Installable Trigger. The frequency will vary from case to case. The OP can make their own assessment. There are other sources that are available to explain this.
Drive API
The OP must activate the Drive API (Advanced Services as well as the Google Cloud Platform API Dashboard). For the sake of clarity, a brief outline of the steps is at the end of this answer.
Code
// GLOBALS
// Array of file extension which you would like to extract to Drive
var fileTypesToExtract = ['xls', 'xlsx', 'xlsm'];
//Name of the folders in google drive in which files will be put
var homeFolder = "009-StackExchange"; // a folder branching from the root
var ExcelFolderName = "010-GmailToDrive"; // sub folder of "homeFolder"
//Name of the label which will be applied after processing the mail message
var emaillabelName = 'GmailToDrive';
function so54755021()
// an adaptation of function GmailToDrive()
{
//build query to search emails
var query = '';
// loop through the filestoextract and add to query
for (var i in fileTypesToExtract) {
query += (query == '' ? ('filename:' + fileTypesToExtract[i]) : (' OR filename:' + fileTypesToExtract[i]));
}
//Logger.log("DEBUG: #01 the query is: " + query); //DEBUG
query = 'in:inbox has:nouserlabels ' + query;
//Logger.log("DEBUG: #02 the query is: " + query); //DEBUG
var threads = GmailApp.search(query);
//Logger.log("DEBUG: threads = " + threads + "; threads length = " + threads.length); //DEBUG
var label = getGmailLabel_(emaillabelName);
//Logger.log("DEBUG: label = " + label); //DEBUG (GmailToDrive)
var parentFolder;
if (threads.length > 0) {
//Logger.log("DEBUG: threads length is more than zero");//DEBUG
//Logger.log("DEBUG: folder name = " + folderName); //DEBUG
//parentFolder = getFolder_(folderName); // subroutine
// build sub-folder if necessary
createDriveFolder(homeFolder, ExcelFolderName);
parentFolder = homeFolder;
}
for (var i in threads) {
var mesgs = threads[i].getMessages();
for (var j in mesgs) {
//get attachments
var attachments = mesgs[j].getAttachments();
for (var k in attachments) {
var attachment = attachments[k];
//Logger.log("DEBUG: attachment: " + attachment);//DEBUG
var isExcelType = checkIfExcel_(attachment);
//Logger.log("DEBUG: isExceltype = " + isExcelType);//DEBUG
if (!isExcelType) continue;
// Copy the Blob
var attachmentBlob = attachment.copyBlob();
//Logger.log("DEBUG: attachmentblob = " + attachmentBlob);//DEBUG
var ExcelFolderObject = DriveApp.getFoldersByName(ExcelFolderName).next();
//Create the Excel file in Google Drive
var file = ExcelFolderObject.createFile(attachmentBlob);
// get the file name and ID
var fileid = file.getId();
var filename = file.getName();
// Logger.log("DEBUG: file = " + file + ", and ID = " + fileid + ", and file name: " + filename);//DEBUG
var fileType = file.getMimeType()
// Logger.log("DEBUG: the MIME type is " + fileType);//DEBUG
// copy the Blob again in preparation for conversion
var xBlob = file.getBlob();
// get the folder ID to copy the file
var folderId = DriveApp.getFoldersByName(ExcelFolderName).next().getId();
// set parameters for the new file
var newFile = {
title: filename + '_converted',
key: fileid,
parents: [{
"id": folderId
}]
}
// convert the file
var convfile = Drive.Files.insert(newFile, xBlob, {
convert: true
});
// Logger.log("DEBUG: the converted file is " + convfile);//DEBUG
}
}
// Add the label to the Gmail item
threads[i].addLabel(label);
}
}
// If necessary, create the label in GMail
function getGmailLabel_(name) {
var label = GmailApp.getUserLabelByName(name);
if (label == null) {
label = GmailApp.createLabel(name);
}
return label;
}
function createDriveFolder(baseFolder, folderName) {
var baseFolderObject = DriveApp.getFoldersByName(baseFolder).next();
//Logger.log("DEBUG: basefolderobject = " + baseFolderObject);//DEBUG
var folders = DriveApp.getFoldersByName(baseFolder).next().getFolders();
//Logger.log("DEBUG: folders: "+folders);//DEBUG
// set variable to detect a match
var foldermatch = 0;
//Loop through folders
while (folders.hasNext()) {
var folder = folders.next();
//Logger.log(DEBUG: folder.getName());//DEBUG
// If the folder name matches
if (folder.getName() == folderName) {
// update the match variable
foldermatch = 1;
// Logger.log("DEBUG: there's a match/folder exists: " + folder.getName());//DEBUG
}
}
// Do something is there is a match
if (foldermatch != 0) {
//Logger.log("DEBUG: There was a match so do NOTHING");//DEBUG
} else {
// Logger.log("DEBUG: There was no match so create the folder"); //DEBUG
baseFolderObject.createFolder(folderName);
}
// The folder already existed, or it has been created. Either way, our work is done.
return;
}
// this function will check for filextension type.
// and return boolean
function checkIfExcel_(attachment) {
var fileName = attachment.getName();
var temp = fileName.split('.');
var fileExtension = temp[temp.length - 1].toLowerCase();
if (fileTypesToExtract.indexOf(fileExtension) != -1) return true;
else return false;
}
Enable the Drive Api
1 - From the Script Editor, select Resources > Advanced Google Services; select Drive API and slide the switch to On.
2 - Click the link to the "Google Cloud Platform API Dashboard"
3 - The Google Cloud Platform - APIs and Services screen opens in a new window.
Click the link to Enable APIs and Services
4 - Search for "drive", and then click the option for Drive API.
5 - Click the "Enable" button for the Drive API
6 - The system will display the Drive API details; note the Drive API is enabled. Then just close this window.
7 - Click OK on the "Advances Google Services" screen (which has remained open all this time).
You're ready to run the script.
