Search code examples
google-apps-scriptgoogle-sheetssvgpng

Google Apps Script to export svg data in cell to png file

I have a google sheet that is set up to generate svg data based on values in other sheets - I use this often for scorecards, leader boards etc. I have this set up so that one cell (for the sake of this example let's say it is "Sheet1!A1") contains all the lines of code for an svg file. I currently have a script which takes this cell and exports it as an SVG.

However, I then have the additional step of converting these svgs into pngs so I can share them on social media etc. Does anyone know of a way to take the svg code in a cell in google sheets and export this as a PNG file? It's definitely wishful thinking on my part, but it would be amazing if this functionality was possible

Solution

  • I believe your goal is as follows.

    • In your situation, the SVG code is put into a cell "A1" on Google Spreadsheet.
    • You want to retrieve the SVG code from the cell "A1" and convert it as a PNG image.
    • You want to achieve this using Google Apps Script.

    In this case, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet and please set your cell as A1Notation to range. And, please enable Drive API at Advanced Google services.

    function myFunction() {
  const range = "Sheet1!A1"; // Please set your cell as A1Notation.

  const svgCode = SpreadsheetApp.getActiveSpreadsheet().getRange(range).getDisplayValue().trim();
  const temp = DriveApp.createFile("sample.svg", svgCode, MimeType.SVG);
  Utilities.sleep(3000); // This might not be required to be used.
  const url = Drive.Files.get(temp.getId()).thumbnailLink.replace("=s220", "=s1000");
  const blob = UrlFetchApp.fetch(url).getBlob();
  DriveApp.createFile(blob.setName("sample.png"));
  // temp.setTrashed(true); // If you want to delete the SVG file, please use this line.
}

    • When this script myFunction() is run, the SVG code is retrieved from a cell of "Sheet1!A1", and an SVG file is created. And, a PNG image is retrieved from the SVG file by converting using Drive API. And, the PNG file is created as a PNG file.

    • In this sample, the PNG file is created in the root folder. If you want to export the PNG file to the specific folder, please modify DriveApp.createFile(blob.setName("sample.png")) to DriveApp.getFolderById("###folderID###").createFile(blob.setName("sample.png")).

    Note:

    • Unfortunately, I cannot know your the svg code. So, when this script cannot be used, please provide your sample the svg code. By this, I would like to confirm it.

    References: