I am using the code found here: How to convert a Google Docs-File to an Excel-File (XLSX) and more specifically in this gist to create an excel file.
I need to modify the code.
- Create an excel file each time a new row is added to the google spreadsheet. Working now
- The new excel file row 1 should be the headers from the source spreadsheet and row 2 should be the new row that was added to the source spreadsheet.
- The name of the file should be based upon the values found in the new row, columns 2 & 15. Working Now
- The new excel file should be placed in folder "Form Responses" id= 0B-eAmBjlnU16YTJIVVFpMjJoTzQ. Working Now
I was able to modify the code to trigger when a new row was added, to use the values from the last row, columns 2 & 15 as the name of the file and move the newly created file to a specific folder (steps 1, 3 & 4) but am stuck on step 2. The function works when manually run its just not triggering when I would expect it to (On spreadsheet change, edit or form submit)
function test_downloadXLS() {
var fileId = SpreadsheetApp.openById("ID").getId();
downloadXLS( fileId);
var range = SpreadsheetApp.getActiveSheet().getRange('A2:DI2');
var files = DriveApp.getRootFolder().getFiles();
while (files.hasNext()) {
var file = files.next();
var destination = DriveApp.getFolderById("0B-eAmBjlnU16YTJIVVFpMjJoTzQ");
destination.addFile(file);
var pull = DriveApp.getRootFolder();
pull.removeFile(file);
range.clear()
}
}
/**
* Downloads spreadsheet with given file id as an Excel file.
* Uses Advanced Drive Service, which must be enabled.
* Throws if error encountered.
*
* From https://stackoverflow.com/a/27281729/1677912
*
* @param {String} fileId File ID of Sheets file on Drive.
*/
function downloadXLS(fileId) {
var file = Drive.Files.get(fileId);
var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];
var options = {
headers: {
Authorization:"Bearer "+ScriptApp.getOAuthToken()
},
muteHttpExceptions : true /// Get failure results
}
var response = UrlFetchApp.fetch(url, options);
var status = response.getResponseCode();
var result = response.getContentText();
if (status != 200) {
// Get additional error message info, depending on format
if (result.toUpperCase().indexOf("<HTML") !== -1) {
var message = strip_tags(result);
}
else if (result.indexOf('errors') != -1) {
message = JSON.parse(result).error.message;
}
throw new Error('Error (' + status + ") " + message );
}
var ss = SpreadsheetApp.openById('16Vv_FqrZyeCmCoK9XAvujVRisa-5m3FabH5uNAKprYc');
var sh = ss.getSheets()[0];// access first sheet (0 indexed)
var row = sh.getLastRow();
var data = sh.getRange(row, 2).getValue();
var row2 = sh.getLastRow();
var data2 = sh.getRange(row, 15).getValue();
var doc = response.getBlob();
DriveApp.createFile(doc).setName(data + "-" + data2 + '.xlsx');
}
// A JavaScript equivalent of PHP’s strip_tags
// from http://phpjs.org/functions/strip_tags/
function strip_tags(input, allowed) {
allowed = (((allowed || '') + '')
.toLowerCase()
.match(/<[a-z][a-z0-9]*>/g) || [])
.join(''); // making sure the allowed arg is a string containing only tags in lowercase (<a><b><c>)
var tags = /<\/?([a-z][a-z0-9]*)\b[^>]*>/gi,
commentsAndPhpTags = /<!--[\s\S]*?-->|<\?(?:php)?[\s\S]*?\?>/gi;
return input.replace(commentsAndPhpTags, '')
.replace(tags, function($0, $1) {
return allowed.indexOf('<' + $1.toLowerCase() + '>') > -1 ? $0 : '';
});
}
I didn't have much luck finding information on how to export only a specific range as an excel file (Step 2) so I tried a different approach with some success but ran into another problem.
I created a second spreadsheet which has the same headers as the first. In the first spreadsheet I use the following code to copy a new row to this new spreadsheet:
function initializeTrigger()
var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger("myFunction")
.forSpreadsheet(sheet)
.onChange()
.create();
}
function myFunction(e){
Logger.log(e.changeType);
if(e.changeType=='INSERT_ROW'){
var ss = SpreadsheetApp.openById('ID');
var ssd = SpreadsheetApp.openById('ID');
var sourceSheet = ss.getSheetByName('Responses');
var lastrow = sourceSheet.getLastRow();
var sourceData = sourceSheet.getRange(lastrow, 1, 1, 53).getValues();
ssd.appendRow(sourceData[0]);
}
}
In the new spreadsheet, the second one, I tried the original code seen at the beginning of this post but it's not working. I think the problem here might be the fact that I'm adding a row, exporting it and then deleting the row so the next time a new row is copied over the log is not seeing anything new in terms of new rows?
I also tried the code referenced here in this gist and it works when run manually but I can't figure out a trigger which would work as soon as the new row is copied over.
