Extract images from Google Sheet to Drive and reinsert as links in other or next cell

Question

Here A post I found I try it but its didn't work or maybe I did something wrong I asked them but they didn't reply and delete the my post Can you please help on it

Extract In-line images from Google Sheet and reinsert as links

My Sample File

https://docs.google.com/spreadsheets/d/1OMvyuzT6gvSyJ84IN4jctOZkijuR4yNo8BjeMcbPhC8/edit#gid=0

CHART CELL Extract images and send them to in Google Drive folder and create all images and reinsert them as links in the C column (optional Trigger it once a day at 9.15 am and delete images at 3.30 pm) or monthly will do or we do manually

While Extract Images to drive the Image Pixel size can be changed as I need

Answer 1

I believe your goal is as follows.

• You want to retrieve the chart created by SPARKLINE function as an image and save it as an image file. And, put the file URL in column "C".

Issue and workaround:

Unfortunately, in the current stage, the chart created by SPARKLINE function cannot be retrieved as an image. So, in this answer, I would like to use a workaround. The flow of this workaround is as follows.

1. Retrieve formulas from column "B".
2. Create a chart using the formula with Google Apps Script.
3. Retrieve a blob from the chart.
4. Create the chart as an image file.
5. Put the file URL in column "C".

When this flow is reflected in a sample script, it becomes as follows.

Sample script:

Before you use this script, please set the folder ID of the folder you want to create the image file.

function myFunction() {
  const folderId = "###";  // Please set your folder ID.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("DATA");
  const range = sheet.getRange("A2:B" + sheet.getLastRow());
  const values = range.getValues();
  const formulas = range.getFormulas();
  const temp = ss.insertSheet("tempSheet");
  sheet.getRange("A2:A").copyTo(temp.getRange("A2"));
  const folder = DriveApp.getFolderById(folderId);
  const urls = values.map(([v], i) => {
    const formula = formulas[i][1];
    if (formula.toUpperCase().includes("SPARKLINE")) {
      const f = formula.match(/\(([\w\s\S]*)\)/);
      if (f) {
        temp.getRange("B1").setFormula(`=${f[1]}`);
        SpreadsheetApp.flush();
        const r = temp.getRange("C1:C" + temp.getLastRow());
        const [, ...vv] = r.getValues().map(([c]) => c);
        const max = Math.max(...vv);
        const min = Math.min(...vv);
        const chart = temp.newChart()
          .setChartType(Charts.ChartType.SPARKLINE)
          .addRange(r)
          .setOption("showAxisLines", false)
          .setOption("showValueLabels", false)
          .setOption("vAxis", { textPosition: 'none', viewWindow: { max, min } })
          .setOption("width", 200)
          .setOption("height", 100)
          .setPosition(1, 1, 0, 0)
          .build();
        temp.insertChart(chart);
        r.clear();
        const blob = chart.getBlob().setName(`${v}.png`);
        temp.removeChart(chart);
        const file = folder.createFile(blob);
        return [file.getUrl()];
      }
    }
    return [null];
  }, []);
  sheet.getRange(2, 3, urls.length).setValues(urls);
  ss.deleteSheet(temp);
}

Testing:

When this script is run, the image files are created from the formulas of column "B". And, the file URLs are put into column "C". The filename of image file is created using the values of column "A". And, you can see the sample images of the created image files in the following image.

Note:

• This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

• About your new request of While Extract Images to drive the Image Pixel size can be changed as I need, in this case, please modify .setOption("width", 200) and .setOption("height", 100) for your situation.

References:

• insertChart(chart)
• Class EmbeddedChart