Error while using your lib :/

[Clemteys]

Hello sir, Thank you for your useful work. However, I can't figure out why, while using your lib, I'm getting the following error :

"stack":"TypeError: Cannot read properties of null (reading 'getValue')
at ExcelApp:133:51\n at Array.map () at ExcelApp.getSharedStrings (ExcelApp:132:56) at ExcelApp.parsXLSX (ExcelApp:112:24) at new ExcelApp (ExcelApp:21:18) at SpreadsheetAppp.getImages (SpreadsheetAppp:38:18)", "message":"Cannot read properties of null (reading 'getValue')"

Would you mind pointing me in the right direction ? Thank you again for your work.

My code (pretty basic) : let file = DocsServiceApp.openBySpreadsheetId('<ID>') console.log(file.obj.spreadsheetId) let allImages = file.getImages() The SpreadSheet id logged looks fine, the error "This file ID is not the file ID of Spreadsheet." is not triggered

[newjie]

At first I am getting the same error, and it seems this error has to do with other content on the sheet. Watch this: https://www.loom.com/share/4254cf14032c40b0806554a7d607a7cd By changing the text color of a parenthesis this error is gone but I am getting another error:

Here is the link to my test sheet:https://docs.google.com/spreadsheets/d/1OtlQNu7cKelnXBJk2gdCXHJkT0ixNugXfaBkQfQzxto/edit#gid=297574336

The script runs well with the first sheet ('test", however with second sheet, I am getting the above error.

[Clemteys]

Thank you for your answer. Indeed, if I clear the formatting of the sheet, the error is gone. My workaround will be to make a copy of the sheet, clear the formatting, get the images and then delete the copy.

For your information, a dirty trick (but working) I was using in the meantime was to access the last spreadsheet revision, and fetch the published version for images.

function dirtyWorkaround(spreadsheetId) {
  //  get latest revision
  let revisions = Drive.Revisions.list(spreadsheetId).items
  let revisionId = revisions[revisions.length-1].id
  
  Drive.Revisions.update({
    published: true,
    publishedOutsideDomain: true,
    publishAuto: true
  }, spreadsheetId, revisionId)

  // Use of Cheerio lib to fetch revision
  const $ = Cheerio.load(
    UrlFetchApp
      .fetch('https://docs.google.com/spreadsheets/u/0/d/${spreadsheetId}/pub')
      .getContentText()
  );

  let images = [];

  $('img').each(function () {
    const imageBlob = UrlFetchApp
      .fetch($(this).attr('src'))
      .getBlob();
    images.push(imageBlob);
  });

  // Unpublish after use
  Drive.Revisions.update({
    published: false,
    publishedOutsideDomain: false,
    publishAuto: false
  }, spreadsheetId, revisionId);

  // Retourner le tableau d'images.
  return images;
}

[newjie]

I don't quite understand your code and I am not even sure where your code is supposed to be run. It seems to me that it is a javascript code instead of App script code, is it? Have you also experienced the error in my screenshot? It seems that you have found the cause of this error and fixed it with your code. Did you?

[newjie]

Also, lots of SO questions are saying it is not possible to get blob of the overgridimages but obviously this library does. I am very curious how it manages to do it.

[Clemteys]

Hello, I don't experienced the error in your screenshot. You should update the lib to the latest version tho, it might fix it.

Google App Script is javascript-based. You can paste my code in the Google App Script Editor. It will need the Drive service and the Cheerio lib (https://github.com/tani/cheeriogs) to run properly.

It published the spreadsheet as HTML, fetches the content using Cheerio, finds all image elements in the HTML, stores the image data in an array, and then updates the revision to be unpublished again. Pretty dirty I know^^

[newjie]

I think I am using the latest version

"It published the spreadsheet as HTML, fetches the content using Cheerio, finds all image elements in the HTML, stores the image data in an array, and then updates the revision to be unpublished again. Pretty dirty I know^^"

This sounds to me that it doesn't need the DocsServiceApp any more, right?