Automatically export Google Spreadsheet to Excel
<p>I want a Google Apps Script that automatically exports a Google Spreadsheet that I have on Google Drive to <code>.xls</code> or <code>.xlsx</code> 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 <a href="https://stackoverflow.com/questions/19398819/export-google-spreadsheet-to-xlsx-every-time-an-edit-is-made">here</a> on Stack Overflow but since the function <code>oAuthConfig</code> is
discontinued, I think I would open a new thread based on <code>OAuth1</code> library.</p>

<p>Here are all the steps I followed:</p>

<ol>
<li><p>While the file was open I went to <code>Tools -&gt; Script Editor...</code> and then I followed these <a href="https://developers.google.com/apps-script/guide_libraries" rel="nofollow noreferrer">instructions</a> to install the latest <code>OAuth1</code> library (v.12 and the library
project key I used is <code>Mb2Vpd5nfD3Pz-_a-39Q4VfxhMjh3Sh48</code>). I have also tried with the <code>Development mode</code> enabled or disabled.</p></li>
<li><p>Then I tried to change the above script based on all the changes of the Google API (see <a href="https://developers.google.com/apps-script/migration/oauth-config" rel="nofollow noreferrer">here</a>, <a href="https://code.google.com/p/google-apps-script-issues/issues/detail?id=5017" rel="nofollow noreferrer">here</a>, <a href="https://developers.google.com/apps-script/reference/drive/drive-app#createfileblob" rel="nofollow noreferrer">here</a>, and <a href="https://developers.google.com/apps-script/reference/drive/file#setnamename" rel="nofollow noreferrer">here</a>).</p></li>
<li><p>I ran every function except <code>googleOAuth_(...)</code> and granted whatever permissions they asked for.</p></li>
<li><p>Finally, I created a trigger from <code>Resources -&gt; Current project's triggers</code> which calls the function <code>myOnEdit</code> on every edit to the spreadsheet and notifies me on e-mail if it fails.</p></li>
</ol>

<p>Here is the script:</p>

<pre><code>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+'&amp;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+'&amp;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"};
}
</code></pre>

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

<ol>
<li><p>Even if the file <code>newfile.xls</code> 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.</p></li>
<li><p>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.</p></li>
</ol>