Automatically export Google Spreadsheet to Excel

Question

I want a Google Apps Script that automatically exports a Google Spreadsheet that I have on Google Drive to .xls or .xlsx by using a trigger that is either time-driven (say every 30 minutes) or event-driven (on every edit made on file). I was based on an old question here on Stack Overflow but since the function oAuthConfig is discontinued, I think I would open a new thread based on OAuth1 library.

Here are all the steps I followed:

While the file was open I went to Tools -> Script Editor... and then I followed these instructions to install the latest OAuth1 library (v.12 and the library project key I used is Mb2Vpd5nfD3Pz-_a-39Q4VfxhMjh3Sh48). I have also tried with the Development mode enabled or disabled.
Then I tried to change the above script based on all the changes of the Google API (see here, here, here, and here).
I ran every function except googleOAuth_(...) and granted whatever permissions they asked for.
Finally, I created a trigger from Resources -> Current project's triggers which calls the function myOnEdit on every edit to the spreadsheet and notifies me on e-mail if it fails.

Here is the script:

function myOnEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 1 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, ss.getSpreadsheetTimeZone(), "MM/dd/yy, hh:mm:ss");
    var id = ss.getId();
    s.getRange('A' + row.toString()).setValue(time); 
    var url = 'https://docs.google.com/feeds/';
    var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                                googleOAuth_('docs',url)).getBlob()
    DriveApp.createFile(doc).setName('newfile.xls')
  }
}

function authorise(){
  // function to call to authorize googleOauth
  var id=SpreadsheetApp.getActiveSpreadsheet().getId();
  var url = 'https://docs.google.com/feeds/';
  var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                            googleOAuth_('docs',url)).getBlob()
}

function googleOAuth_(name,scope) {
  var service = OAuth1.createService(name);
  service.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  service.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  service.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  service.setConsumerKey('anonymous');
  service.setConsumerSecret('anonymous');
  return {oAuthServiceName:name, oAuthUseToken:"always"};
}

So, it seems that the code doesn't produce any errors but there are 2 problems:

Even if the file newfile.xls is created, it is not what I want. Basically, when I open it on Excel I get the message "The file format and extension of "newfile.xls" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?", then it waits for a while and when the file opens it seems like the Google Login page in Excel format.
Each time I make an edit on a row, the cell that corresponds to the first column of that row changes to the current timestamp like "07/03/16, 03:58:30" on the original spreadsheet.

If you manually export the file to XLSX, are you able to open it? Have you considered to use the content service instead of UrlFetch? — Rubén, Jul 03 '16 at 16:58
@Ruben Yes I can. Either by the way you proposed or by the `File` menu. — mgus, Jul 03 '16 at 17:01
Related: http://stackoverflow.com/questions/31809987/google-app-scripts-email-a-spreadsheet-as-excel — Rubén, Jul 03 '16 at 17:34

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

To generate an XLSX file on your Google Drive periodically, you can set up a timed trigger calling the test_downloadXLS() function from the gist referenced in How to convert a Google Docs-File to an Excel-File (XLSX).

edited May 23 '17 at 11:58

Community

1
1

answered Jul 04 '16 at 15:22

Mogsdad

44,709
21
151
275

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Partial Short answer

XLS is not supported by Google Sheets/Google Drive.

Explanation

AFAIK .XLS is not supported by the current version of Google Sheets. Anyway I just tried a URL with the following structure directly on my browser's address bar:

https://docs.google.com/spreadsheets/d/spreadsheet-key/export?exportFormat=xls

and got

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 03 '16 at 14:28

Rubén

34,714
9
70
166

OK that's correct. But if you replace `xls` with `xlsx` then you will see that this is supported. I also tried this on the script (replaced all occurences) but this time I couldn't even open the file. I got the error "Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." So there has to be a problem in the script. – mgus Jul 03 '16 at 16:10
1

As you can see, my answer is a partial answer as I only mentioned one of the issues with the script. – Rubén Jul 03 '16 at 16:57

Automatically export Google Spreadsheet to Excel

2 Answers2

Partial Short answer

Explanation