Google Docs Script editor checking if a formula exists in a cell

Question

I am trying to check a range of cells in a row to see if one is blank, if it is, I am inserting an image, if it is not blank, I am attempting to move to the next cell in the row to check that until the range is finished.

So far I have this:

function gfc() {
var stockitem = '=image("imageurlhere")';
var item1 = getValue('g4');
var item2 = getValue('h4');
var item3 = getValue('i4');
if (item1 == ''); {
setValue('g4', stockitem);
}
else if (item2 == '') {
setValue('h4', stockitem);
}
else {
setValue('i4', stockitem);
}
}

The problem I am having is that the code works if I am inserting text, but if I am inserting the image it counts the image as a blank cell and re-inserts the image in the already image filled cell.

E.G. If I insert an image into g4 because it was blank, that image would then be counted as blank again and re-inserted without going to the next step. I know I am missing something simple, any help will do, thank you!

A side comment, it looks like you have defined functions like setValue('range') and getValue('range') to set and get single values. It's worth mentioning that you may also want to look at using the built in range.getValues() and setValues() functions, which let you operate on more than one cell at a time via Arrays. Your current approach limits you to one cell at a time, which will be slow if you are operating on many cells. Generally you want to read/write from the sheet as little as is practical. — Cameron Roberts
– Cameron Roberts, Commented Dec 15, 2016 at 21:09
Cameron, you are correct. I had range at first that I was experimenting with, but it would set all of the cells equal to an image. I wanted to make it so that it checks one cell, if it is blank inserts the image and finishes. If it is not blank it would move to the next cell and add that there unless all of the designated cells were filled. — Austin
– Austin, Commented Dec 16, 2016 at 17:21

Serge insas · Accepted Answer · 2016-12-15 22:13:52Z

2

there is a separate getFormula() call that must use to retrieve formulas from a cell.

See: https://developers.google.com/apps-script/reference/spreadsheet/range#getformula

Typically getValue() would return the result of the formula in the cell, but I suppose it doesn't return anything useful with the =image() formula.

edited Dec 15, 2016 at 22:13

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

answered Dec 15, 2016 at 21:03

Cameron Roberts

7,4251 gold badge23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Austin Over a year ago

Thank you very much! I knew it had to be something simple I was missing. It works perfectly.

Collectives™ on Stack Overflow

Google Docs Script editor checking if a formula exists in a cell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related