Looping through cells in Google Spreadsheet

Question

I've been busy trying to use the build-in javascript in Google Spreadsheet, however, not having worked in either javascript or Google Spreadsheet, i'm having a few difficulties.

My script is supposed to read a number (1-3) in a cell, and from that number parse an image to the cell below (I've been using the setFormula command for this).

So far it's working for 1 cell (B6 as i've choosen right now), but i would like to loop through a column with numbers in every other cell (So that after the script has run, it's number-picture-number-picture etc) - i just can't figure out how.

The code i'm using right now:

    function numbtoimage() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var url = 'IMAGE("https://dl.dropboxusercontent.com/s/bpqy8o796casqjl/belt.JPG?dl=0", 2)';
var url2 = 'IMAGE("https://dl.dropboxusercontent.com/s/4q8sakhkpot0h65/belt2.JPG?dl=0",2)';
var url3 = 'IMAGE("https://dl.dropboxusercontent.com/s/kvsf4z6z45rcg53/belt3.JPG?dl=0",2)';
var cell = sheet.getRange("B6")
var data = cell.getValue()
if(data==1) {cell.offset(1, 0, 1).setFormula(url);}
else if(data==2) {cell.offset(1, 0, 1).setFormula(url2);}
else if(data==3) {cell.offset(1, 0, 1).setFormula(url3);}
}

I've looked at This similar problem, but have been unable to make it work for my case.

Any help is greatly and truly appreciated!

Nicklas

score 0 · Accepted Answer · edited Jan 21 '19 at 11:32

You need some sort of loop to go through the data. I Would suggest a FOR loop. Your script is currently written to get one single cell value, rather than all the values. So it might be an idea to get all values in one go, then check whats in them.

Also from you question, it's not clear where the numbers will be found. Only in column B?

Here is a quick example (untested), that goes through column B looking for a number and it should insert the link in the cell below based on that number. This code is based on your original example and untested but hopefully it helps.

function numbtoimage() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var url = 'IMAGE("https://dl.dropboxusercontent.com/s/bpqy8o796casqjl/belt.JPG?dl=0", 2)';
  var url2 = 'IMAGE("https://dl.dropboxusercontent.com/s/4q8sakhkpot0h65/belt2.JPG?dl=0",2)';
  var url3 = 'IMAGE("https://dl.dropboxusercontent.com/s/kvsf4z6z45rcg53/belt3.JPG?dl=0",2)';
  var values = sheet.getValues();

  for(i=0; i < values.lenth ; i++){

    if(values[i][1]==1) {sheet.getRange(i+2, 2).setFormula(url);}
    else if(values[i][1]==2) {sheet.getRange(i+2, 2).setFormula(url2);}
    else if(values[i][1]==3) {sheet.getRange(i+2, 2).setFormula(url3);}
  }
}

Looping through cells in Google Spreadsheet

1 Answers1