1

Previous StackOverflow posts report that UrlFetchApp terminates after exceeding a minute of runtime. This has occured to me and is affecting my program. This program automatically converts a spreadsheet into an excel file as backup and to save data. The spreadsheet has a lot of data so it now times out. What are my alternative solutions to this program? My google spreadsheet is not immensely large and does not meet the maximum threshold of google sheets, so it doesn't necessitate the difficulty of clearing data. 

function downloadSpreadSheet(){
  var spreadsheet_id =  "INSERT_ID_HERE";    //spreadsheet_id is the id of the google sheet, found in URL
  var ss = SpreadsheetApp.openById(spreadsheet_id);
  var now = new Date();

  var driveFolder = 'Dispatch Backup';                                     //driveFolder is the name of the destination folder on Google Drive
  var newFilename = 'Exported ' + (now.getMonth()+1) + '-' + now.getDate() + "-" + now.getYear();  //newFilename is the name of the .xlsx file to be written out
  var folder = DriveApp.getFoldersByName(driveFolder);                     //folder is a collection of all folders in the user's Drive with that name


  var params = {                                                           //params are the parameters used to grant authority to copy and save the new file
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
  };


  //url is html around the spreadsheet ID that converts the sheet into Excel format
  var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key="+spreadsheet_id+"&exportFormat=xlsx";
  var doc = UrlFetchApp.fetch(url, params).getBlob();   //TIMES OUT HERE                                 //doc contains the converted sheet data

  //finds the location of driveFolder or creates one if not present
  if (folder.hasNext()) {
    folder = folder.next();
  } else {
    folder = DriveApp.createFolder(driveFolder);
  }

  //Creates a file in the root of the user's Drive from a given Blob of arbitrary data.
  file= folder.createFile(doc);
  //Sets the name of the File.
  if (newFilename != "")
          file.setName(newFilename)

}

Any help or advice is appreciated. Thank you.

Mahmoud Maarouf
  • 185
  • 1
  • 12
  • 1
    Try a other method that using the feed url. For details checkout the Mogsdad answer to https://stackoverflow.com/q/27277058/1595451 – Rubén Jan 06 '20 at 03:49
  • Thank you for providing that. I have tried his code and it still failed. He uses UrlFetchApp in his script, so it isn't surprising that I got a timeout error once again. – Mahmoud Maarouf Jan 06 '20 at 08:40

1 Answers1

0

Doing some research about your issue I encountered other people have been having the same issue and it has been reported on issue tracker, you could comment there to keep alive the thread.

alberto vielma
  • 2,302
  • 2
  • 8
  • 15
  • Hi alberto, thank you for this. It appeared to work, until I downloaded it and got an error while opening it stating: "Excel cannot open the fie 'FILE_NAME' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file" . Do you know what the issue is? – Mahmoud Maarouf Jan 07 '20 at 07:00
  • I'm editing my answer because for exporting the file into .xlsx, it must be used the [Files: export](https://developers.google.com/drive/api/v2/reference/files/export) endpoint, but with Apps Script there is a bug reported on [issue tracker](https://issuetracker.google.com/issues/36765129) – alberto vielma Jan 08 '20 at 11:35