0

I need to insert images at a fixed width to keep organized on the sheet. I have been testing, just getting one image where I need it, and have so far failed.

My current version works sometimes; other times, it displays the image three times, some scaled and some not. I am struggling to troubleshoot because I don't fully understand what the code is doing.

I am hoping this is a ridiculously easy fix.

I started basic, just getting the image in:

function sized_image()  {
  var ss = SpreadsheetApp.getActive();
  var img = ss.getRange('B3').getValue();
  ss.insertImage(img,4,4).
}

That worked great. I feel like I am really close, but I can't seem to get it to resize the images. This is the current version of my code:

function sized_image()  {
  var ss = SpreadsheetApp.getActive();
  var img = ss.getRange('B3').getValue();
  var finalimg = ss.insertImage(img,6,6);
  var orgW = finalimg.getWidth();
  var orgH = finalimg.getHeight();
  var tgtW = 500
  var ratio = orgW / tgtW
  var newH = orgH / ratio
  ss.insertImage(img,4,4).setWidth(tgtW).setHeight(newH);
  finalimg.setWidth(tgtW).setHeight(newH);
}

Note: I have ss.insertimage(img,4,4) as a different image location for troubleshooting.

I realize this is a pretty basic code, and sadly, I only got here through a lot of googling and am now finding myself lost after adapting a few different solutions.

All images are Google Drive images, img is the http://drive.google.com/uc?export=view&id= link.

Janos
  • 1
  • 1
  • In your situation, is this thread useful? https://stackoverflow.com/q/58958716 – Tanaike Aug 21 '23 at 00:06
  • I think that if your sheet has four questions or another kind of content is irrelevant from a programming-solving point of view; focus your wording on the programming problem to be solved. – Rubén Aug 21 '23 at 01:48
  • What part of the code do you don't understand? – Rubén Aug 21 '23 at 01:54
  • @Rubén The only thing that I don't understand is why I get the error "getWidth is not a function" for img.getWidth, but not finalimg.getWidth. I understand all the steps in the code, just not why I can't get or set the img width & height. – Janos Aug 21 '23 at 18:12
  • Add the textual error message directly to the question body. Also, add links to relevant Stack Overflow questions and why they don't meet your needs. – Rubén Aug 21 '23 at 18:21

2 Answers2

0

Stop overthinking. Use an image editor to create files with the size you need instead of calculating the dimensions and changing the image each time they are inserted. This will be a one-time job instead of something to do multiple times.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • I certainly would, but I have 500+ images to deal with, and as I am using scripts to insert the images anyway, this seemed easier... unless it's not? – Janos Aug 21 '23 at 18:14
  • @Janos You could automate the image creation. It might look to be more difficult at this time, but this approach might prevent some problems in the short them and in the long term it will be a lot better. – Rubén Aug 21 '23 at 18:20
0

OK. I have it largely fixed. Turns out the multiple images, was due to them not being displayed/ visible after the script ran. for some reason, it required a reload or sheet change before they were visible. So the multiple versions was simply prior runs. I don't know why, but if someone wants to figure that out, go ahead, I am simply putting the images on another worksheet to eliminate that problem.

Here is the final version of the code.

function sized_image1()  {
  var ss = SpreadsheetApp.getActive();
  var tgtSheet = ss.getSheetByName("Actions");
  var img = ss.getRange('B3').getValue();
  var finalimg = tgtSheet.insertImage(img,2,2);
  var orgW = finalimg.getWidth();
  var orgH = finalimg.getHeight();
  var tgtH = 500
  var ratio = orgH / tgtH
  var newW = orgW / ratio
  finalimg.setWidth(newW).setHeight(tgtH);
}

I am sure there are easier ways to do this. But as the only responses refused to offer any coding support, I had to work it out on my own. And it gets the job done, so I am moving on.

Maybe a noob isn't welcome here, that's fine. Maybe I asked the wrong questions, also fine. But when the answer was basically, "you don't need the second to last line" all of the other, "don't tell me about the project" or "resize the images first" were of zero help.

so welcome to my first and last post here.

Janos
  • 1
  • 1