Google Apps Script Tutorial to Download Images From URL to Sheets Cell & Google Drive

Welcome folks today in this blog post we will be downloading images from url to sheets cell and rows and also we will be uploading images to google drive in javascript. All the full source code of application is shown below.

Get Started

In order to get started you need to create a new google spreadsheet and inside it you need to insert some data which will be the image url’s. These urls can be from pixabay and unsplash as shown below. We need to create the picture url column and we need to insert the urls of the picture as shown below

And now we need to go to tools and select Apps Script editor and we need to write the below code as shown below

And now we need to copy paste the below code inside the code.gs file as shown below

code.gs

function insertImage() {

  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  let lastRow = sheet.getLastRow(); 

  for (let i = 0; i < lastRow-1; i++) {
    let url = sheet.getRange(2+i,1).getValue(); 
    let image = SpreadsheetApp.newCellImage().setSourceUrl(url); 
    sheet.getRange(2+i,2).setValue(image); 
  }
}

function insertImage() {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

let lastRow = sheet.getLastRow();

for (let i = 0; i < lastRow-1; i++) {

let url = sheet.getRange(2+i,1).getValue();

let image = SpreadsheetApp.newCellImage().setSourceUrl(url);

sheet.getRange(2+i,2).setValue(image);

}

As you can see we are writing the insertImage() function inside it we are first of all getting the current google sheet and then we are getting the current row. And then we are getting the reference of the last row. And after that we are using the for loop to iterate over all the rows and inside it we will be getting the url of the images and then we are downloading the actual image from the url and inserting it to a new column. And after that we are setting the downloaded image to it’s other column as shown below in the gif animation

Uploading Images From Sheet to Google Drive

First of all we need to create a new folder inside your google drive where you will be storing all the downloaded images from the sheets. And then you need to copy paste the folder id as shown below

After that you need to copy paste the code inside the code.gs file as shown below

code.gs

function downloadImage() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  let lastRow = sheet.getLastRow(); 

  let folder = DriveApp.getFolderById("1XsVZoyADqioGGMsFSuK27iqBSqbnaSY2"); 

  for (let i = 0; i < lastRow-1; i++) {
    let url = sheet.getRange(2+i,1).getValue(); 
    let blob = UrlFetchApp.fetch(url).getBlob(); 
    folder.createFile(blob); 
  }
}

function downloadImage() {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

let lastRow = sheet.getLastRow();

let folder = DriveApp.getFolderById("1XsVZoyADqioGGMsFSuK27iqBSqbnaSY2");

for (let i = 0; i < lastRow-1; i++) {

let url = sheet.getRange(2+i,1).getValue();

let blob = UrlFetchApp.fetch(url).getBlob();

folder.createFile(blob);

}

As you can see in the above code we are using the for loop to iterate over all the rows and columns of the urls present of the images and then we are using the fetch() method to download the image as a BLOB and it can directly be downloaded in the google drive folder as shown below

function downloadImage() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  let lastRow = sheet.getLastRow(); 

  let folder = DriveApp.getFolderById("1W4pv0RW7DB_yh8pqChc8YPNyq0ETQ_1m"); 

  for (let i = 0; i < lastRow-1; i++) {
    let url = sheet.getRange(2+i,1).getValue(); 
    let blob = UrlFetchApp.fetch(url).getBlob(); 
    folder.createFile(blob); 
  }
}

function downloadImage() {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

let lastRow = sheet.getLastRow();

let folder = DriveApp.getFolderById("1W4pv0RW7DB_yh8pqChc8YPNyq0ETQ_1m");

for (let i = 0; i < lastRow-1; i++) {

let url = sheet.getRange(2+i,1).getValue();

let blob = UrlFetchApp.fetch(url).getBlob();

folder.createFile(blob);

}