Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

converting base64 image text in a cell to an image inserted in that specific cell

say i have a cell containing a base64-image text and i want to convert that into its actual image, is there a way to code that in google sheet?

my problem was i am able to turn it into a blob image with the actual image i want but i dont know how to insert it in a specific cell.

Here's my code: function insertImageFromBase64Src2() { var data = 'iVBORw0KGgoAAAANSUhEUgAAAYYAAACVCAYAAABctV1kAAAAAXNSR0IArs4c6QAAD+ZJREFUeF7tnb0LNFcVxk+6WAgpLOyilRaCFhZ2UbCwUxCxkmgpFkGwiIWoWFjoH5BgoekCCkZsTGUCKV67pFNENGCRgEWsTCEoT5jH3HfcfXfmzJ3ZmTO/gWWX3bkf53fu3ud+z2PBBQEIQAACEGgIPAYNCEAAAhCAQEsAYaA87JnAR4bMfTYinmwyqu//FhGvDu/6zAUBCHQigDB0AnnCaFQ56/XUYLsrcX/fIlHFrZcq+LYS12fd/8IQz8sR8fggAo5P97w5ROaw/k1pK05d/k1xvRsRD0ZpndBFmAyBHAGEIcftLKFUAbetdVf6rph/MYBQxe3K35X9lFa8W/7jyn1K2NYHrSjp+6cj4ocIw1mKKXb2JoAw9CZ6nPjaFr8qdrW+XcHaClfQ7ZDN3Er7HkS+PwwzvXKPxEkTAkcngDAc3YPT89+2/t1Cfz4i3hpa1vq9SkX688Em9Rq4IACBmQQQhpnADnS7hcDj8Kr01fLX+xFa/VnUsvv3w1CSh7qycREOAqckgDDUcbsqRI2t6/p6IwCajK0sBGMPShR0fa6Oa7EEAtsSQBi25d0ztVYIPDTU9gp6pnWUuDSEJC6IwlE8Rj53SQBh2KVbrmbKYqAegS4NlZytR3ANjkTh7Yh47mQ9pGOVYHJ7CAIIwyHcFFpl4zkDiQGTqg/7TcNH4vONQhPoxyiZ5LIkAYRhv25tewf0DK77CVHYbxkmZwclgDDsz3ESBPUQNG9A7+C6f8TJcwr0FPZXjsnRgQkgDPtxnoRAgqAVRJo3qLKnYC3C7iloovlMq67W4km8EPgfAYTh/oXBLV9Vbmr5ct0mwPDRbUbcAYE0AYQhjW5xQK0s0r4DBGEeSomCelfqKdCrmseOuyEwiQDCMAlT15s8ZKRKjdVF89AiCvN4cTcEUgQQhhS2VCBPKr8TEd9OxXDuQIjCuf2P9RsSQBjWh21B8Dp7JkrnM0cU5jMjBATSBBCGNLqbAb0PQUNHGjJiPPwmsv+7oV2Syuqj+fwIAYEUAYQhhe2RgdqNaRIETvjMMZagqqcgQeXsoxxDQkEgRQBhSGG7GAhB6MdSK7Z+FBHfQ1j7QSUmCEwlgDBMJXX9PgRhOcM2Bm3ykzB8LSJe6xs1sUEAAlMIIAxTKF2/x5UYR1cs4+jQEgQx5YiLPjyJBQIpAghDCtt7G6xUgbEXIcfvUiiJgs4+YuNaP6bEBIEUAYRhHrZ2pRETovPYPepuTzQjCv2YEhME0gQQhunoVHmpRctKo+nMptyJKEyhxD0Q2JAAwjANto/BppcwjdfUuxCFqaS4DwIbEkAYHg3bG6yYS+hfKBGF/kyJEQJdCCAM1zF66IgVMl2K2kORIAr9mRIjBLoRQBguo9TQ0RMcdtetnLURsfpoFaxECoF+BBCGh1kydNSvbF2KiZ7CunyJHQJdCCAM72Nkc1WXInU1Eg/NfXTdZIgdAhBYSgBhiHAvQSxZdbS0RF0Ojyisw5VYIbAKgbMLg4+0YG/CKsXrvUi9SxzRXY8xMUOgK4GzCoMnQPXQHIY2uhaphyJDFNZjS8wQWI3A2YShffALB9+tVqzoKayLltghsC6BMwmDho1+MLw0dMS1HgF6CuuxJWYIrE7gDMKgXoKeBKaLzWqrFynmFNZHTAoQWJdAdWGgl7Bu+RnHTk8hz1vzXprz4tngeYaE7ESgqjB4eaQw8RD5ToXlRjSIwjLOOrnXvdplMREaAgsJVBSG70TET5hLWFgy5gWXKHwrIr4yLxh3NwTUu9XF/BfF4u4EKgmDW6yaU2AuYbuiJe5PD8y3S7VeSuoxaCgJYajn28NZVEUY9KfSGK1WHfHH2q4YHuE5FWoo+Go/67tPR8Svhgp5O2qXU/pPRFT5P96bJekvJHD0gthuVKOXsLAwzAyulV5q4Yr7Fte4gn9qVOHrd7/0k/KmS9/ps16PDy/H9XxEPLtF5m+koXL8JI2aHXiCLLxH4KjC4APv9Aenl7B9YZYovBAR2iQ493Kl3FbiqhTby/e8ERFfvFDhu6JvBeDNRgD8vcqJLomIhry86kd5t3DMzf8a9/+VHfhrYCXOLIEjCYP+5BrL1h9cl3cu7+kPnvXDkcJJFDRcd2lZZVvZu0Vvf/m9tbX1nSt7t/B138tDC1+fld64F6A4ff+HI+LBUEZebd73vgSU3sKRSv9J8rpEGP4cEf+OiI+PWl96wM3rTevNLbnM+mz9aVTBuOUnMdCfPtNSPYlLVzPTx4n8JiLeiYhPRoRa9PKPfmsrflfybctefnNLfqqYW2jc4ndP4lK870bEi6tZv07EYvaFnQxnrWMhsR6SwBJhUMtRf8a3GsslEmq5jSf5dIvu+91QsbtiaFuAbmG6Mmh7BqpU2hbjIWEfKNOu6D3Eo8rLDYCXIsJib5Pkm7alnzXV6boxoPKhRoDSq+Z/9n1kSwnhViewRBgelbnxRKD+6OpJfOmGRW4J/iUiXitYGazu0JkJtH5yq7xt1UuQ5bdnVloCrMrRDYJ256+EoHKvEFGYWVC5fVsCawnDLdFwb8AtUvcItrX+nKlJDDxXIz+oJe5hvvGkrHeQ99g93oqABUkNAaWpKzPUeEQPIgpH9NrJ8nwPYTgZ4l2Y653JWrfvydhb+z16VGCeI1Jc3x2GE9v5gV3A2TATPMluQ9gklSeAMOTZ7S3keAJYvQL3AOZWxl4BNvepa+1ksZYReydv5WGhqeUAUZhKivvuTgBhuLsLumTAjyj1RO2Sijizm9nDRBYDpb+3vQJdQCcj6dH7SiZNMAjMJ4AwzGe2pxDe+f3HiPhmh3F6rTTTWP+tYSYx8FyFxMBzBNlNb3ti2jsvfhbI3N5X73wQHwQmE0AYJqPa5Y1q3X9m2ATmCX3PIeh96qS+9yhc27hm48di4OWkU4RklwBXzJSHjsRmSQ9uxSwSNQQuE0AYapWMdp7BK488v6CewCWhmCIKusfDVRYDhoqulx0Px90S2lqlD2vKEEAYyrjyqiHj5altj+KfEfHVYcXQpeWiFgNvXvMxJPWp5Sz0GV70EnL8CLUTAgjDThyxUTbancWfigi9dHkHuncXf2DY66Bd7D5B9Sz7DDKu8DMpOK4lQ48wuyOAMOzOJZtkyLuM2yNJJBrqPaiS0/HUOu5EZyLp3ZfE4e2I+NeM+YtNDLpTIh5iU/JbHT9+J1NJ9kwEEIYzefv9lUTj+QHPM3gntMfGfeZVe2qqdqvrPh9j7d3Lez/FtKenPTwngeU5ID3JEtcuCCAMu3DDJpm4dryFn343dzNae9DeWCiuTXRvYujKiWjeRUt0eQ7IyqCJ/n4EEIb7sd8y5UsbrFSxa429l1IuXXLazl/4c3v89tFPyPWeEe/zYM5lyxJMWpsSQBg2xX2XxMbHW7RLTzUMstYa+0s9CgHwkJOE4gjDTx5m0zursu5ShEl0awIIw9bEt01vfLxFuxdBojD1gTk9cu35Ch+f0e652KNYtJv51Dtg53KPUkAchyCAMBzCTalMtj0FD4PMnUdIJTwj0Ph5EO0QlKLxXMVavZprWfU8Akt1ZziTW+sQQBjq+LK1RK3yHw9PzGsfi7p0HmELWo8Si7WFohUEho228DZp7JIAwrBLtyzKlETh18MehB6nrS7KTKfAl8RCUXvZ7dKJ4HaHN6uNOjmNaI5LAGE4ru8u5Vyi8NuI+OnEE1KPbr13HMsOiccfhl7SVKEYCwLnPx29RJD/LgQQhi4YdxOJzzTaTYY2zEg7ua1kVel7852eK+1VUPpOBwz6uHC9IwgbOoqk9k8AYdi/j8jhMgLqVVg0nhnOh9IxHw+aye2pPYxlOSE0BA5CAGE4iKPIZppAu+y0PTJc36sn0c5f+BDBNwfR2HI5b9pAAkKgNwGEoTdR4tsLAc8/6H3K7u7xBLfs8MOPWsE4wqa8vfiAfByUAMJwUMeR7YsE2l3dqsw1d+BKPYusFQwfIKi4JBCe09Fn9TIQjSxlwu2KAMKwK3eQmSQBb+ZTC7/3ZHI7oe+5inE2LR4amtLVCoY+vxERL42+T5pKMAisTwBhWJ8xKaxHoD3YTr2DR+2QlmjowUN61oRa/r58fLgrd39/TQTG1rQ9B/3mieyXI+LLEfH3IYCebeEHI32oieQTw56T1yPiTxHx3MZHlaznHWI+LAGE4bCuO3XGfbCdj/gYTxKPz2FSS17C4TkDV+aCaJHQUNClSv8WaPcOLCSKT58lDB9rehCt4Eg8HO6JiPhgRPxjuOHZWwnyOwTWJoAwrE2Y+HsT+GVEfH4YmtHeBFfsrvSVnvcl+Njv3nkgPgiUJoAwlHZvGeNU6f9sGArS0MyLI8v8rIcyBmMIBO5JAGG4J33SnkqgfYzo1DDcBwEIJAkgDElwBIMABCBQlQDCUNWz2AUBCEAgSQBhSIIjGAQgAIGqBBCGqp7FLghAAAJJAghDEhzBIAABCFQlgDBU9Sx2QQACEEgSQBiS4AgGAQhAoCoBhKGqZ7ELAhCAQJIAwpAERzAIQAACVQkgDFU9i10QgAAEkgQQhiQ4gkEAAhCoSgBhqOpZ7IIABCCQJIAwJMERDAIQgEBVAghDVc9iFwQgAIEkAYQhCY5gEIAABKoSQBiqeha7IAABCCQJIAxJcASDAAQgUJUAwlDVs9gFAQhAIEkAYUiCIxgEIACBqgQQhqqexS4IQAACSQIIQxIcwSAAAQhUJYAwVPUsdkEAAhBIEkAYkuAIBgEIQKAqAYShqmexCwIQgECSAMKQBEcwCEAAAlUJIAxVPYtdEIAABJIEEIYkOIJBAAIQqEoAYajqWeyCAAQgkCSAMCTBEQwCEIBAVQIIQ1XPYhcEIACBJAGEIQmOYBCAAASqEkAYqnoWuyAAAQgkCSAMSXAEgwAEIFCVAMJQ1bPYBQEIQCBJAGFIgiMYBCAAgaoEEIaqnsUuCEAAAkkCCEMSHMEgAAEIVCWAMFT1LHZBAAIQSBJAGJLgCAYBCECgKgGEoapnsQsCEIBAkgDCkARHMAhAAAJVCSAMVT2LXRCAAASSBBCGJDiCQQACEKhKAGGo6lnsggAEIJAkgDAkwREMAhCAQFUCCENVz2IXBCAAgSQBhCEJjmAQgAAEqhJAGKp6FrsgAAEIJAkgDElwBIMABCBQlQDCUNWz2AUBCEAgSQBhSIIjGAQgAIGqBBCGqp7FLghAAAJJAghDEhzBIAABCFQlgDBU9Sx2QQACEEgSQBiS4AgGAQhAoCoBhKGqZ7ELAhCAQJIAwpAERzAIQAACVQkgDFU9i10QgAAEkgQQhiQ4gkEAAhCoSgBhqOpZ7IIABCCQJIAwJMERDAIQgEBVAghDVc9iFwQgAIEkAYQhCY5gEIAABKoSQBiqeha7IAABCCQJIAxJcASDAAQgUJUAwlDVs9gFAQhAIEkAYUiCIxgEIACBqgQQhqqexS4IQAACSQL/BTZRVrTb2JToAAAAAElFTkSuQmCC'; var imageBlob = Utilities.newBlob(Utilities.base64Decode(data), 'image/png', 'sample'); // Modified var ss = SpreadsheetApp.getActiveSpreadsheet(); //This code is bound to a Sheet var po = { shName:'Sheet1', column:2, row:2 } var sh = ss.getSheetByName(po.shName); var image = sh.insertImage(imageBlob, po.column, po.row);//Insert an image and return the image }

i expect the formed blob to be inserted inside a cell and not outside the cell

Solution

  • If your base64 data is valid image data, how about using it with Class CellImage as the data URL? When this is reflected in your script, it becomes as follows.

    Modified script:

    function insertImageFromBase64Src2() {
  var data = '###'; // Your base64 data

  var sheet = SpreadsheetApp.getActiveSheet();
  var image = SpreadsheetApp.newCellImage().setSourceUrl(`data:image/png;base64,${data}`).build();
  sheet.getRange(2, 2).setValue(image);
}

    • When I tested this modified script using your base64 data, I confirmed that an image is put to cell "B2". It is as follows.

      enter image description here

    • getRange(2, 2) is from your script of var po = { shName:'Sheet1', column:2, row:2 }.

    Reference: