Absolute legend!!!!!!!!!!!!!!!!!!!!!!!!!! I can't believe you can just type the code and it works like a charm. Thank you so much, made my job way easier today.
Awesome code, thank you. After you have downloaded the pictures to a Drive folder, can you please develop the code and show us how to insert the new Drive link of the picture in a different column?
Hello! This was majorly helpful! So my question is: If the link stops working will the image in the cell still be there OR should I essentially replace the link with a link of a downloaded image in google drive?
This was very useful can I ask if we can add new coloumn with the a shareable google drive link for the newly downloaded images? And THANK YOU very much for this helpful tutorial 🙏🏻
I'd love to be able to rename the downloaded image files based on data in another column - to expand on your example, rename them as partnumber_01, partnumber_02... .jpg/png etc.
This is perfect. Thank you! I do have another question about using url images but with google slides. Can I create a new slide per row of data and save the images from the url to a slide image placeholder (using the same shape as the placeholder, meaning circle or some mask). Is this doable?
This has absolutely saved me hours and hours of work! thank you so much! But i have a question.. My google sheet contains more than 3000 images. How can i eliminate the runtime error of 6 minutes? much appreciated.
This is a tough one. There are 3 things you can do: 1. Refactor your code to run more efficiently by optimizing the code to run within 6 minutes. 2. Convert your script to run as a batch operation, where the script's process is spread out over multiple executions, where the time slice for each execution is under the 6 minute limit. Time-based triggers should help with this. 3. Offload the time-intensive parts of the process to a 3rd party service; and have your script call out to it with the appropriate parameters.
What a great Channel/Teacher!!! Sir, I hope you had a nice summer or even still have one ☺️ I am fascinated about the way you teach and even more about your response to comments. May I kindly "hop in" to this topic? 😇 Is there any chance to to show us, how to render those pics to a published web-app? Whatever I try as a "noob" to show those pics from a spreadsheet in an web-app " fails. :(((
Hi Thomas, thanks for the kind words. And I'm always happy to help solve problems. Can you give me a little more information as to what kind of web app you want to publish to?
@@jsphpalumbo good afternoon, thx for your kind response and sorry for my delay (struggled a bit due to COVID) Well, my main target is to display/render a image. My cause I want to "create" a CRUD App for my garden. Nothing commercial!!! I want to keep track, what I did, when to fertilize again, etc.... Therefore i would "implement" pictures/images which would be stored in a different folder at my Google drive. A little hint/snippet how to render those pics would be nice. Imagine a spreadsheet like this: Id - name - description - pic - Link (where the image is stored) Who to render those in a list or even a "card"(Grid)?
Is it possible In the mean time when the photo is being downloaded IT ALSO RENAME THE IMAGES WITH CUSTOM NAMES. For example cell A1 ==> "CUSTOM NAME" cell B2==> "IMAGE URL" when I run the download script the image will be downloaded with the renamed text which was listed in A1. Please Help me
This video was amazing! How do you change the file name? For instance I would love to add 2 Cells as the name like 223234 - This was the best So i can sort based on number?
This video has been a huge help and you are a wonderful teacher! I am using method 2 but my source sheet has many rows where the url field is empty. The script stops at an empty cell. How do I tell script to ignore empty cells in the url column? Many thanks!
Hi there, glad this tutorial was helpful. To prevent your script from stopping on blank cells, I would add an IF statement that says something like if ( cellValue != "") { Do Something}. This will allow your script to skip over any blank cells without stopping.
Thanks and I have it working@@jsphpalumbo but the sheet is over a thousand rows and taking longer to finish than the 6 min allotment of time. So that's what I am working on now. Thanks again for your help and expertise!
@@jsphpalumbo Hello, I would like to implement this function, but where exactly should I put it? Here's what I did but it doesn't work. function insertImage() { let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); let lastRow = sheet.getLastRow(); for (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 downloadImage() { let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); let lastRow = sheet.getLastRow(); let folder = DriveApp.getFolderById("1W4pv0RW7DB_yh8pqChc8YPNyq0ETQ_1m"); for (i = 0; i < lastRow-1; i++) { let url = sheet.getRange(2+i,1).getValue(); let name = sheet.getRange(2+i,3).getValue(); let blob = UrlFetchApp.fetch(url).getBlob(); folder.createFile(blob).setName(name); if (cellValue != "") Do something; } } Can you help me ?
Getting "Error retrieving image from URL or bad URL" when I try to retrieve images from a google drive folder. I have a form that automatically saves images in a google drive folder but when i use the drive link to the image, I keep getting this error. Anyone else having this problem?
Thanks, bro. You just solved my problem by just casting a spell like a wizard... btw, is there any way to tell the script skip blank cells rather than reporting an error? I saw you answered the 404 one, that helps also. Anyways, thanks for your great help!
This is very helpful and explained in a very clear way as all of your videos. Can you show the same technique instead of a URL address, using a file address on a windows 10 computer?
Thank you sir! I need to add a script that i want. i would like to see shared google drive urls in the third columbs for each downloaded images. can you describe?
Hi, this is a really great video, but can you download images into sorted folders? For example I want some of my images to de downloaded into a folder named "1" and then have some images download into a folder named "2" and so on?
Place the name on the third column and replace the loop code for this: for (let i = 0; i < lastRow-1; i++) { let url = sheet.getRange(2+i,1).getValue(); let name = sheet.getRange(2+i,3).getValue(); let blob = UrlFetchApp.fetch(url).getBlob(); folder.createFile(blob).setName(name); }
Great video, followed along, copied the code, and I get a getactivesheet error stopping the script immediately. Do I need to grant access to the sheet?
Hello Joseph, after you have downloaded the pictures to a Drive folder, can you show us how to insert the new Drive link of the picture in a different column?
i am trying to do this for videos but when it downloads to my folder it shows as view.html or edit.html depending on what i have past the code. When I remove view or edit it still does the same. Any help here?
Hi, Joseph, this is great! Do you know of a way to then take the images that have been pulled into the cell and have them inserted into a google docs template using tags?
@joseph, please help Getting "Error retrieving image from URL or bad URL" when I try to retrieve images from a google drive folder. Link i'm using int sheets is a dropBox link
This is really helpful. I have encountered an error in line 3 (Code.gs:3) which says that paranthesis after getactivespreadsheet as null. Do you have any solution for this?
Hi Nishan, my first guess is that you're not calling the spreadsheet correctly. To grab the active spreadsheet should look like this let ss = SpreadsheetApp.getActiveSpreadsheet();
Hi, Joseph. Thanks a lot, this video is great. I was wondering if you could add a few things, I have this column with links, that now with your video i can download, but, I want to download each of them in to different folders and with specific names, let said we have three columns: the image links, the folder name and the image name I need for each image. Is that possible? Im sure is not a problem for you! Thanks in advance!
Place the name on the third column and replace the loop code for this: for (let i = 0; i < lastRow-1; i++) { let url = sheet.getRange(2+i,1).getValue(); let name = sheet.getRange(2+i,3).getValue(); let blob = UrlFetchApp.fetch(url).getBlob(); folder.createFile(blob).setName(name); }
Place the name on the third column and replace the loop code for this: for (let i = 0; i < lastRow-1; i++) { let url = sheet.getRange(2+i,1).getValue(); let name = sheet.getRange(2+i,3).getValue(); let blob = UrlFetchApp.fetch(url).getBlob(); folder.createFile(blob).setName(name); }
Hello, when I try to run the code I get an error message saying "TypeError: Cannot read property 'getLastRow' of null." How can I solve this issue? Thanks.
Hi Joachim, and thank you for your question. Yes, you can can set muteHttpExceptions to TRUE in the HTTP request to suppress these errors. It should look like this: ``` var response = UrlFetchApp.fetch("yourdomain.com/404", {muteHttpExceptions: true}); if ( response.getResponseCode() == 404 ) { Logger.log("Webpage not found"); ``` Let me know if that helps.
Place the name on the third column and replace the loop code for this: for (let i = 0; i < lastRow-1; i++) { let url = sheet.getRange(2+i,1).getValue(); let name = sheet.getRange(2+i,3).getValue(); let blob = UrlFetchApp.fetch(url).getBlob(); folder.createFile(blob).setName(name); }
Is it possible In the mean time when the photo is being downloaded IT ALSO RENAME THE IMAGES WITH CUSTOM NAMES. For example cell A1 ==> "CUSTOM NAME" cell B2==> "IMAGE URL" when I run the download script the image will be downloaded with the renamed text which was listed in A1. Please Help me
This has absolutely saved me hours and hours of work. I sincerely appreciate this very thorough tutorial and code.
Glad it helped!
This saved us an incredible amount of time. Thank you, Joseph!
Absolute legend!!!!!!!!!!!!!!!!!!!!!!!!!! I can't believe you can just type the code and it works like a charm. Thank you so much, made my job way easier today.
Glad I could help! And I've had a lot practice writing code for Apps Script, so not as impressive as you might think ;)
Thanks Joseph. This was incredibly helpful, clear, and consise. Much appreciated!
Awesome code, thank you. After you have downloaded the pictures to a Drive folder, can you please develop the code and show us how to insert the new Drive link of the picture in a different column?
OMG IT WORKED!!! Thank you! ♥♥
you're a freaking life-saver!!! A LIFE-SAVER!!!!
Hello! This was majorly helpful!
So my question is: If the link stops working will the image in the cell still be there OR should I essentially replace the link with a link of a downloaded image in google drive?
This was very useful can I ask if we can add new coloumn with the a shareable google drive link for the newly downloaded images? And THANK YOU very much for this helpful tutorial 🙏🏻
I'd love to be able to rename the downloaded image files based on data in another column - to expand on your example, rename them as partnumber_01, partnumber_02... .jpg/png etc.
Woah, thanks Joseph! Great video, simple and easy to understand. 💪😸
This is perfect. Thank you! I do have another question about using url images but with google slides. Can I create a new slide per row of data and save the images from the url to a slide image placeholder (using the same shape as the placeholder, meaning circle or some mask). Is this doable?
Brilliant work, thanks!
This has absolutely saved me hours and hours of work! thank you so much! But i have a question.. My google sheet contains more than 3000 images. How can i eliminate the runtime error of 6 minutes? much appreciated.
This is a tough one. There are 3 things you can do:
1. Refactor your code to run more efficiently by optimizing the code to run within 6 minutes.
2. Convert your script to run as a batch operation, where the script's process is spread out over multiple executions, where the time slice for each execution is under the 6 minute limit. Time-based triggers should help with this.
3. Offload the time-intensive parts of the process to a 3rd party service; and have your script call out to it with the appropriate parameters.
Hello! Thank you! It works. However, the images I’ve downloaded are in html format. Can you explain what possibly could be the reason?
What a great Channel/Teacher!!!
Sir, I hope you had a nice summer or even still have one ☺️
I am fascinated about the way you teach and even more about your response to comments.
May I kindly "hop in" to this topic? 😇
Is there any chance to to show us, how to render those pics to a published web-app?
Whatever I try as a "noob" to show those pics from a spreadsheet in an web-app " fails. :(((
Hi Thomas, thanks for the kind words. And I'm always happy to help solve problems. Can you give me a little more information as to what kind of web app you want to publish to?
@@jsphpalumbo good afternoon, thx for your kind response and sorry for my delay (struggled a bit due to COVID)
Well, my main target is to display/render a image.
My cause I want to "create" a CRUD App for my garden. Nothing commercial!!!
I want to keep track, what I did, when to fertilize again, etc....
Therefore i would "implement" pictures/images which would be stored in a different folder at my Google drive.
A little hint/snippet how to render those pics would be nice.
Imagine a spreadsheet like this:
Id - name - description - pic - Link (where the image is stored)
Who to render those in a list or even a "card"(Grid)?
Is it possible In the mean time when the photo is being downloaded IT ALSO RENAME THE IMAGES WITH CUSTOM NAMES. For example cell A1 ==> "CUSTOM NAME" cell B2==> "IMAGE URL" when I run the download script the image will be downloaded with the renamed text which was listed in A1. Please Help me
This video was amazing! How do you change the file name? For instance I would love to add 2 Cells as the name like 223234 - This was the best So i can sort based on number?
This video has been a huge help and you are a wonderful teacher! I am using method 2 but my source sheet has many rows where the url field is empty. The script stops at an empty cell. How do I tell script to ignore empty cells in the url column? Many thanks!
Hi there, glad this tutorial was helpful. To prevent your script from stopping on blank cells, I would add an IF statement that says something like if ( cellValue != "") { Do Something}. This will allow your script to skip over any blank cells without stopping.
Thanks and I have it working@@jsphpalumbo but the sheet is over a thousand rows and taking longer to finish than the 6 min allotment of time. So that's what I am working on now. Thanks again for your help and expertise!
@@jsphpalumbo Hello,
I would like to implement this function, but where exactly should I put it?
Here's what I did but it doesn't work.
function insertImage() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lastRow = sheet.getLastRow();
for (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 downloadImage() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lastRow = sheet.getLastRow();
let folder = DriveApp.getFolderById("1W4pv0RW7DB_yh8pqChc8YPNyq0ETQ_1m");
for (i = 0; i < lastRow-1; i++) {
let url = sheet.getRange(2+i,1).getValue();
let name = sheet.getRange(2+i,3).getValue();
let blob = UrlFetchApp.fetch(url).getBlob();
folder.createFile(blob).setName(name);
if (cellValue != "") Do something;
}
}
Can you help me ?
Hello Joseph
Can you help me ? I really need to make the script work even when there is an empty cell.
Thanks a lot for your help !
problem solved , thanks
Getting "Error retrieving image from URL or bad URL" when I try to retrieve images from a google drive folder. I have a form that automatically saves images in a google drive folder but when i use the drive link to the image, I keep getting this error. Anyone else having this problem?
i have also same problem has your issue resolved??????
can you set images' file name on download with corresponding cell in the sheet?
Thank you so much!! This was super helpful 🙏😊
You're so welcome!
Thanks, bro. You just solved my problem by just casting a spell like a wizard...
btw, is there any way to tell the script skip blank cells rather than reporting an error?
I saw you answered the 404 one, that helps also.
Anyways, thanks for your great help!
This is very helpful and explained in a very clear way as all of your videos.
Can you show the same technique instead of a URL address, using a file address on a windows 10 computer?
Hi Garry, thanks for the comment. I've never done that before, but let me dig into that for you. Stay tuned.
hello sir
can i also download Images from google drive link with are in Google excel sheet
Thank you sir!
I need to add a script that i want.
i would like to see shared google drive urls in the third columbs for each downloaded images. can you describe?
Thank you Sir. This is very helpful
Awesome code, thank u. I want to ask u for another way to one click for download all pictures 🤔
Hi, this is a really great video, but can you download images into sorted folders? For example I want some of my images to de downloaded into a folder named "1" and then have some images download into a folder named "2" and so on?
Amazing perfect saved loads of time.
Glad this was useful!!
How can we give the downloaded file a specific name from a column?
Place the name on the third column and replace the loop code for this:
for (let i = 0; i < lastRow-1; i++) {
let url = sheet.getRange(2+i,1).getValue();
let name = sheet.getRange(2+i,3).getValue();
let blob = UrlFetchApp.fetch(url).getBlob();
folder.createFile(blob).setName(name);
}
Thank you for all your tutorials.
Can you please show us how to download the images to our laptop.
Thank you
After following the second half of the tutorial, you can just download the google drive folder onto your computer
Great video, followed along, copied the code, and I get a getactivesheet error stopping the script immediately. Do I need to grant access to the sheet?
Very nice video. Please help > google sheet text & image link data multiple invoice/marksheets/student result/others create pdf file.
Wow... this was very helpful
Glad you think so!
Error
TypeError: Cannot read properties of null (reading 'getActiveSheet')
how to fix this..?
Hello Joseph, after you have downloaded the pictures to a Drive folder, can you show us how to insert the new Drive link of the picture in a different column?
how to adding an image in a cell of google sheet and display it in website by search engine via app script??????????? pls make a video for it
Its work Thank you sir
i am trying to do this for videos but when it downloads to my folder it shows as view.html or edit.html depending on what i have past the code. When I remove view or edit it still does the same. Any help here?
Hi, Joseph, this is great! Do you know of a way to then take the images that have been pulled into the cell and have them inserted into a google docs template using tags?
check this czcams.com/video/YlduiAfhhGQ/video.html will help you
@joseph, please help Getting "Error retrieving image from URL or bad URL" when I try to retrieve images from a google drive folder. Link i'm using int sheets is a dropBox link
can u add to downlowd to multiple folders
i have the column A with some codes and need to download the pictures into the folder with the code name
Hi, I am getting just open.html instead of every image. Please help.
This is really helpful. I have encountered an error in line 3 (Code.gs:3) which says that paranthesis after getactivespreadsheet as null. Do you have any solution for this?
Hi Nishan, my first guess is that you're not calling the spreadsheet correctly. To grab the active spreadsheet should look like this
let ss = SpreadsheetApp.getActiveSpreadsheet();
Hi, Joseph. Thanks a lot, this video is great. I was wondering if you could add a few things, I have this column with links, that now with your video i can download, but, I want to download each of them in to different folders and with specific names, let said we have three columns: the image links, the folder name and the image name I need for each image. Is that possible? Im sure is not a problem for you! Thanks in advance!
Place the name on the third column and replace the loop code for this:
for (let i = 0; i < lastRow-1; i++) {
let url = sheet.getRange(2+i,1).getValue();
let name = sheet.getRange(2+i,3).getValue();
let blob = UrlFetchApp.fetch(url).getBlob();
folder.createFile(blob).setName(name);
}
Hello Please help me, in function download image , how can i auto rename the files
Place the name on the third column and replace the loop code for this:
for (let i = 0; i < lastRow-1; i++) {
let url = sheet.getRange(2+i,1).getValue();
let name = sheet.getRange(2+i,3).getValue();
let blob = UrlFetchApp.fetch(url).getBlob();
folder.createFile(blob).setName(name);
}
@@AndrePiazza THANKS BRO!
Hello, when I try to run the code I get an error message saying "TypeError: Cannot read property 'getLastRow' of null." How can I solve this issue? Thanks.
I keep getting this too:/
Thank you for this usefull script ! Is there a way to make the script continue even if some URL return 404 ?
Hi Joachim, and thank you for your question.
Yes, you can can set muteHttpExceptions to TRUE in the HTTP request to suppress these errors. It should look like this:
```
var response = UrlFetchApp.fetch("yourdomain.com/404", {muteHttpExceptions: true});
if ( response.getResponseCode() == 404 ) { Logger.log("Webpage not found");
```
Let me know if that helps.
Joseph - How can we give the downloaded file a specific name from a column?
Place the name on the third column and replace the loop code for this:
for (let i = 0; i < lastRow-1; i++) {
let url = sheet.getRange(2+i,1).getValue();
let name = sheet.getRange(2+i,3).getValue();
let blob = UrlFetchApp.fetch(url).getBlob();
folder.createFile(blob).setName(name);
}
can you make a video for how to get data from gmail inbox to Google sheet.
Hi Sujit. I can definitely make that video. What kind of data do you want to pull into a sheet?
I think I'm doing something wrong, it's not working :(
Nice :)
Thanks!
Thanks for the video! I am getting TypeError: folder.createFile is not a function. Not sure what is happening?
Is it possible In the mean time when the photo is being downloaded IT ALSO RENAME THE IMAGES WITH CUSTOM NAMES. For example cell A1 ==> "CUSTOM NAME" cell B2==> "IMAGE URL" when I run the download script the image will be downloaded with the renamed text which was listed in A1. Please Help me
czcams.com/video/ty7de6-6lGk/video.html this will solve your prblm