Get Positions of Images in an Excel table in terms or Row ID and Column ID using Office Scripts

Question

I have an excel sheet downloaded from a 3rd party API which has got a image column with images in each row. The image object is named as "Picture 1" on every image under the image column. Now I would like to generate the base64 string of this image so that I can use this image in other data sources like SharePoint, SQL Server etc. I figured out how to convert it to base 64. The problem I have is I am unable to get the position of this image in each row so I can define the base64 string of this image in the same row on a new column called "base64 text". I understood getRange function is not supported for images, charts or shapes. But how do I get the position of the image now? I tried searching all over but couldn't find anything. Here are the steps I would like to do using office script.

1. Get Row number of the image. Name the object with row number like "Picture 1" for row 1, "Picture 2" for row 2.
2. Once I have the row number, I would like to get the shape or image at this position and get base64 text of this image under "base64 text" column.
3. Use this base64 string in other data sources.

Please help me with the directions. Thanks a lot in advance.

Answer 1

• Office Script doesn't provide a method to get topleft cell of shape.
• Create a UDF to get topleft cell
• Convert image to Base64 string and write to the next column

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let shpObjects = selectedSheet.getShapes();
    shpObjects.forEach(shp => {
        let anchorCell: ExcelScript.Range = getTopLeftCell(shp, selectedSheet);
        if (anchorCell) {
            anchorCell.getOffsetRange(0, 1).setValue(shp.getImageAsBase64(ExcelScript.PictureFormat.png));
            shp.setName("Picture_" + (anchorCell.getRowIndex().valueOf() + 1));
        }
    })
}

function getTopLeftCell(shpObject: ExcelScript.Shape,
    shtObject: ExcelScript.Worksheet): ExcelScript.Range {
    // Define search Range, modify as needed
    const max_rows = 100;
    const max_cols = 100;
    const shpTop = shpObject.getTop();
    const shpLeft = shpObject.getLeft();
    let topLeftCell: ExcelScript.Range;
    for (let row = 0; row < max_rows; row++) {
        let cellCur = shtObject.getCell(row, 0).getTop();
        let cellNext = shtObject.getCell(row + 1, 0).getTop();
        if (shpTop >= cellCur && shpTop <= cellNext) {
            for (let col = 0; col < max_cols; col++) {
                let cellCur = shtObject.getCell(0, col).getLeft();
                let cellNext = shtObject.getCell(0, col + 1).getLeft();
                if (shpLeft >= cellCur && shpLeft <= cellNext) {
                    topLeftCell = shtObject.getCell(row, col);
                    break;
                }
            }
            break;
        }
    }
    return topLeftCell;
}

---

• Add a vertical margin to locate the desired top-left cell. This adjustment should resolve the overlap issue with the cell's upper border, as shown in the screenshot provided by OP.

function getTopLeftCell(shpObject: ExcelScript.Shape,
    shtObject: ExcelScript.Worksheet): ExcelScript.Range {
    // Define search Range, modify as needed
    const max_rows = 100;
    const max_cols = 100;
    const vertical_margin = 3; // modify as needed
    const shpTop = shpObject.getTop();
    const shpLeft = shpObject.getLeft();
    let topLeftCell: ExcelScript.Range;
    for (let row = 0; row < max_rows; row++) {
        let cellCur = shtObject.getCell(row, 0).getTop() - vertical_margin;
        let cellNext = shtObject.getCell(row + 1, 0).getTop() - vertical_margin;
        if (shpTop >= cellCur && shpTop <= cellNext) {
            for (let col = 0; col < max_cols; col++) {
                let cellCur = shtObject.getCell(0, col).getLeft();
                let cellNext = shtObject.getCell(0, col + 1).getLeft();
                if (shpLeft >= cellCur && shpLeft <= cellNext) {
                    topLeftCell = shtObject.getCell(row, col);
                    break;
                }
            }
            break;
        }
    }
    return topLeftCell;
}