Skip to content

Instantly share code, notes, and snippets.

@azadisaryev
Forked from soundTricker/convert2Spreadsheet.js
Last active June 23, 2017 09:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save azadisaryev/97dc5e7a975b180a2b42 to your computer and use it in GitHub Desktop.
Save azadisaryev/97dc5e7a975b180a2b42 to your computer and use it in GitHub Desktop.
var KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; //developer key , get on https://code.google.com/apis/console/ and set enable the Drive API.
/**
* Convert from Excel data to Spreadsheet
* @param {Blob} excelFile the excel blob data.
* @param {String} filename file name on uploading drive
* @return {Spreadsheet} spreadsheet instance.
**/
function convert2Spreadsheet(excelFile, filename) {
var oauthConfig = UrlFetchApp.addOAuthService('drive');
//Create oauth config for drive api
var scope = 'https://www.googleapis.com/auth/drive';
oauthConfig.setConsumerKey('anonymous');
oauthConfig.setConsumerSecret('anonymous');
oauthConfig.setRequestTokenUrl('https://www.google.com/accounts/OAuthGetRequestToken?scope='+scope);
oauthConfig.setAuthorizationUrl('https://accounts.google.com/OAuthAuthorizeToken');
oauthConfig.setAccessTokenUrl('https://www.google.com/accounts/OAuthGetAccessToken');
var uploadParams = {
method:'post',
oAuthServiceName: 'drive',
oAuthUseToken: 'always',
contentType: 'application/vnd.ms-excel',
contentLength: excelFile.getBytes().length,
payload: excelFile.getBytes()
};
//insert to root folder of google drive, and convert to Sheets
var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true&key='+KEY, uploadParams);
//this response body format is json , and it has file id. Please see https://developers.google.com/drive/v2/reference/files#resource
var fileDataResponse = JSON.parse(uploadResponse.getContentText());
//Update filename
var updateParams = {
method:'put',
oAuthServiceName: 'drive',
oAuthUseToken: 'always',
contentType: 'application/json',
payload: JSON.stringify({ title: filename })
};
UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id+'?key='+KEY, updateParams);
return SpreadsheetApp.openById(fileDataResponse.id);
}
//Get excel file from site and convert 2 spreadsheet.
var siteUrl = "set your google sites page url";
function convertSiteAttachedExcelFile2Spreadsheet() {
var sitePage = SitesApp.getPageByUrl(siteUrl);
//Get attachments from your page.
var attachments = sitePage.getAttachments();
if(!attachments || attachments.length == 0) {
throw new Error("not found attached files.");
}
//Filter for attachment files.
var excelFiles = attachments.filter(function(attachment) {
//var attachment = attachments[0];
return /.+\.xls$/.test(attachment.getTitle())
});
//var excelFiles = [];
var convertedFiles = [];
//Convert all excel files to spreadsheet.
for(var i = 0; i < excelFiles.length; i++)
var excelFile = excelFiles[i];
Logger.log("converting %s ...", excelFile.getTitle());
convertedFiles.push(convert2Spreadsheet(excelFile.getBlob(), excelFile.getTitle()));
});
Logger.log("Done converting.");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment