1

When I try to insert image in Google Spreadsheet using the below code its working.

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
var image = activeSheet.insertImage(pngUrl, 1, 1);

But when I try to set the width and height of the image, image is not appearing in the Google Spreadsheet. 

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var image = activeSheet.insertImage(pngUrl, 1, 1).setWidth(300).setHeight(300);

It appears only when the browser is refreshed manually.

Cooper
  • 59,616
  • 6
  • 23
  • 54
Geetanjali
  • 458
  • 3
  • 13

2 Answers2

2
  • When an image is put to the sheet with setWidth() and setHeight() using your script, the image is not shown.

If my understanding is correct, how about this answer?

In your script, the image is certainly put to the sheet. But the image is not shown in the sheet. I have experienced this situation. I think that this might be a bug. In that case, how about the following workarounds?

Pattern 1:

When the tab is changed, the image is shown. In this pattern, I used this. When your script is modified, it becomes as follows.

Sample script:

Before you use this script, please add one more sheet with the sheet name of "Sheet2". And this script supposes that the active sheet is not "Sheet2".

var pngUrl = "###"; // Please set this.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();

Logger.log(activeSheet.getImages().length) // <--- 0

var image = activeSheet.insertImage(pngUrl, 1, 1).setWidth(300).setHeight(300);

Logger.log(activeSheet.getImages().length) // <--- 1 By this, it is found that the image is put.

// I added below script.
ss.getSheetByName("Sheet2").activate();
SpreadsheetApp.flush();
activeSheet.activate();

Pattern 2:

In this pattern, after the image is put, the width and height of the image are modified.

Sample script:

var pngUrl = "###"; // Please set this.
var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

activeSheet.insertImage(pngUrl, 1, 1); // Modified
var img = activeSheet.getImages()[0].setWidth(300).setHeight(300); // Added

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thanks @Tanaike for your answer. In the "Pattern 2", the image is getting inserted with the original size. Did it work for you ? – Geetanjali Jul 29 '19 at 08:30
  • @Geetanjali Thank you for replying. Yes. In the pattern 2, I could confirm that the script worked fine. If several images are put in the sheet, as a test, please try to run the script under the condition which has no images. Because this sample script uses the image of 1st index at `getImages()[0]`. Please be careful this. – Tanaike Jul 29 '19 at 23:49
  • @Tanaike, Pattern 2 didn't work for me. – Jayakrishnan Feb 27 '23 at 08:25
  • @Jayakrishnan About `@Tanaike, Pattern 2 didn't work for me.`, I apologize for the inconvenience. When I tested my script, I could confirm that the PNG image could be inserted into the active sheet. So, unfortunately, I cannot replicate your situation. I apologize for my poor skill. But, I would like to support you. So, can you post a new question by including the detailed flow for correctly replicating your current issue? By this, I would like to confirm it. If you can cooperate to resolve your issue, I'm glad. Can you cooperate to do it? – Tanaike Feb 27 '23 at 08:34
  • @Tanaike, no apologies please, you are one of the best Google App script developers I've met. Regarding pattern 2, I can insert the image, but it's not shown by your workaround of modifying height and width. But your Workaround Pattern 1 works fine. only issue is it won't work if user has only single sheet in the spreadsheet. – Jayakrishnan Feb 27 '23 at 09:06
  • @Jayakrishnan Thank you for replying. I cannot understand `only issue is it won't work if user has only single sheet in the spreadsheet.`. Can I ask you about the detail of it? – Tanaike Feb 27 '23 at 11:38
  • In pattern 1, you are activating another sheet 'B' and activating the sheet 'A' with image again. What if spreadsheet contains only one sheet 'A' – Jayakrishnan Feb 27 '23 at 12:40
  • @Jayakrishnan Thank you for replying. I apologize for my poor English skill. Unfortunately, I cannot understand `In pattern 1, you are activating another sheet 'B' and activating the sheet 'A' with image again. What if spreadsheet contains only one sheet 'A'`. But, I would like to try to understand your question. If I could correctly understand it, I would like to think of a solution. I apologize that I cannot resolve various questions soon. I have to study English more. – Tanaike Feb 27 '23 at 13:07
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/252156/discussion-between-jayakrishnan-and-tanaike). – Jayakrishnan Feb 27 '23 at 13:08
  • @Jayakrishnan Thank you for replying. Unfortunately, this is not your question. But, I would like to support you. So, as I have mentioned in my 1st reply to your question, can you post it as a new question by including more information? By this, it will help users including me think of a solution. If you can cooperate to resolve your issue, I'm glad. Can you cooperate to do it? But, from `your Workaround Pattern 1 works fine.` of your reply, if you are not required to post it as a new question. Please don't worry. – Tanaike Feb 27 '23 at 13:20
  • @Tanaike, please check https://stackoverflow.com/questions/75607775/google-apps-script-were-sorry-a-server-error-occurred-while-reading-from-stor – Jayakrishnan Mar 01 '23 at 18:47
0

I slightly improved the solution provided by @Tanaike: Basically, I create a new sheet and hide it immediately, then fill it (with also image) and finally show it to the user. This is a small improvement that allows for reducing the flickering between tabs.

const sheet = spreadsheet.insertSheet(title, 0).hideSheet()
// fill sheet
sheet.showSheet()
Spreadsheet.setActiveSheet(sheet)
al1812
  • 340
  • 1
  • 6
  • 17