BUY FULL SOURCE CODE
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
1 2 3 4 5 6 7 8 9 10 11 |
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
1 2 3 4 5 6 7 8 9 10 11 12 |
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
1 2 3 4 5 6 7 8 9 10 11 12 |
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); } } |
BUY FULL SOURCE CODE