Presumably, the image URLs will be found in the same column; otherwise you have bigger issues with your spreadsheet. Here is a script that converts the URLs from Column C to images.
function convertToImage() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getDataRange();
var colnum = 3; // assuming column C; change to another column number
var cell;
var values = range.getValues();
for (var i=0; i<values.length; i++) {
if (values[i][colnum-1]) {
cell = range.getCell(i+1, colnum);
cell.setFormula('=image("'+values[i][colnum-1]+'")')
}
}
}
Further refinements are possible: extend the range to multiple column (iterate over values[i][j]
), check whether the content is indeed a link (if
statement for values[i][j]
), etc.
I would suggest using dget
in the first sheet, if the number of users is defined, and doesn't grow automatically.
Assuming the second sheet is Sheet2, I would add 2 columns to the first sheet.
The first column would have cells with the formula (changing the cells accordingly, with copy and paste):
=dget(Sheet2!$A:$C,"PAID ACCOUNT",{"USER ID";$A2})
The second column would have cells with the formula (changing the cells accordingly, with copy and paste):
=dget(Sheet2!$A:$C,"ACCOUNT ID",{"USER ID";$A2})
You could also try a more direct approach, by using cell references in the "field" section, such as:
=dget(Sheet2!$A:$C,Sheet2!B$1,{"USER ID";$A2})
After this, you would only need to copy and paste the formula in all the cells that require importing.
If the list grows or is fed from a form, then this solution would not apply.
Best Answer
Try:
(note that concatenate has a limit of 50000 characters)
or
Where list A starts in A1, list B in B1.