How to download files from internet to google drive using google apps script triggers

aabujamra 0
Viewed 45

Based on an URL link I need to download a file to Google Drive with a specific file name. But I need to do that only when I edit an specific column (trigger).

Doing some research in the web I found out how to download files to Google Drive using Google Apps Script. This tutorial is quite helpful in that matter.

I created a Google Sheets sample file for this question in this link. Since we will need to upload files to a folder, I created a sample one too in this link.

I adapted the tutorial code with my file and folder:

function DownloadFile (){

  var fileName=""
  var filesize=""
  
  var response = UrlFetchApp.fetch("https://www.mprj.mp.br/documents/20184/1238340/Rogerio_Pacheco_Alves.pdf")

  var rc = response.getResponseCode()

  if(rc == 200) {
    var fileBlob = response.getBlob()
    var folder = DriveApp.getFolderById("1cWt9e_xT7-8N8uwDPFisQe_4YCPuyCxn")
    if (folder !=null){
      var file = folder.createFile(fileBlob)
      filename = file.getName()
      filesize = file.getSize()
    }
  }
  var fileInfo = {"rc":rc, "filename":fileName, "filesize":filesize}

  Logger.log(fileInfo)
}

I also created a trigger for the function On Edit (for some reason I couldn't set that in the sample Google Sheets, when I try it keeps running but don't conclude the task, but in my project it is set). The problem is that I don't know how to properly connect it to the code so it runs as I want.

So, downloading works fine, however I need to make the following adaptations:

  1. Run it only when column E ("Trigger") in the Google Sheets file is edited
  2. Get the URL from column A ("Link") in the same row as the triggered cell
  3. Get the fileName to save in Google Drive from column D ("FinalName") in the same row as the triggered cell

What would be the appropriate code adaption to include those features?

google-drive-api google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • You want to retrieve the download URL from column "A".
  • You want to retrieve the filename of the download file from column "D".
  • You want to run the script when column "E" is edited.

In this case, how about the following modification? In order to run the script by editing column "E", the installable OnEdit trigger is used.

Modified script:

Please copy and paste the following script to the script editor of your Google Spreadsheet. And, please install OnEdit trigger to DownloadFile.

When you use this script, please edit column "E". By this, the script is automatically run by the installable trigger.

function DownloadFile(e) {
  var sheetName = "Página1"; // Please set the sheet name.

  var { range } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 5) return;
  var [url, , , fileName] = sheet.getRange(range.rowStart, 1, 1, 4).getValues()[0];
  var response = UrlFetchApp.fetch(url);
  var rc = response.getResponseCode();
  if (rc == 200) {
    var fileBlob = response.getBlob().setName(fileName);
    var folder = DriveApp.getFolderById("1cWt9e_xT7-8N8uwDPFisQe_4YCPuyCxn");
    if (folder != null) {
      var file = folder.createFile(fileBlob);
      // filename = file.getName(); // It seems this is not used.
      filesize = file.getSize();
    }
  }
  var fileInfo = { "rc": rc, "filename": fileName, "filesize": filesize };
  Logger.log(fileInfo);
}
  • When this script is used, when column "E" is edited, a file is downloaded using the URL from column "A", and saved the file to the specific folder. At that time, the filename is used from column "D".

Note:

  • This script is run using the event object of OnEdit. So, when you directly run this script, an error like Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.

Reference:

Tanaike0
Related