Google Sheets. How to get the real range size in pixels

Viewed 154

I'm working on a project and need your help!

My script converts the selected range into an image, please see. It first creates a public PDF URL and then converts it to PNG.

enter image description here

It works well for small ranges (10-20 rows) and creates a shot including images, charts, sparklines, and formatting.

The problem is with big ranges (100-1000 rows). They contain a border of unknown size and I cannot calculate it.

enter image description here

Heavy borders make rows higher so the image does not fit.

enter image description here

If we have no borders or thin borders, the real image size appears a bit smaller than calculated. This creates an empty space below the image.

My code sample for getting the range size in pixels:

  // get row height in pixels
  var h = 0;
  for (var i = rownum; i <= rownum2; i++) {
    if (i <= options.measure_limit) {
      size = sheet.getRowHeight(i);
    }
    h += size
    /** manual correction */
    if (size === 2) {
      h-=1;
    } else {
      // h -= 0.42; /** TODO → test the range to make it fit any range */
    }
    
    if ((i % 50) === 0 &&  i <= options.measure_limit) {
      file.toast(
        'Done ' + i + ' rows of ' + rownum2,
        '↕Measuring height...');
    }
  }
  if (i > options.measure_limit) {
    file.toast(
      'Estimation: all other rows are the same size',
      '↕Measuring height...');
  }

As you see, I have to loop over all rows which is extremely inefficient. I'd be glad to hear your ideas for code optimization. Now it loops the first 150 rows and next it assumes all other rows have the same height.

Sample Situations

"Small" ranges are that you can see on screen. "Big" ranges have 100+ rows so they do not fit normal screen. As I create screenshots, I tested all possible range sizes.

Case1 - no borders or thin borders

If I select a big range I get the image, and see it has a white space at the bottom. This means the real size of image was slightly smaller than one I get from the Script by calling sheet.getRowHeight(i).

Case1 - heavy borders

If I select a big range I get the image, and see not all rows I've selected are on that image. Some rows at the bottom of the range are missing. This means when I add heavy borders, the real size of rows is bigger than one I get from the Script by calling sheet.getRowHeight(i).

Conclusion

I'd be glad to hear any ideas including javascript hacks to remove empty space below the image. If it is currently not possible, please also answer with links to docs.

Cheers!

1 Answers

I believe your goal is as follows.

  • You want to export the range as an image using Google Apps Script and Javascript.
  • In order to achieve this, in this question, you want to calculate the row height of the selected cell range.

Issue and workaround:

As our discussions in the comment, in the current stage, when the correct row height of the cell range is trying to be obtained, there are several problems as follows.

  • When the border is used for the cells, it seems that the row height + the border size is different from the exported result. Ref
  • Pixel size might not be changed linearly with the value of row height and border size. Ref
  • When I tested the cell size including the borders, I thought that the tendency of change of size might be different between height and width. Ref
  • When the row height is the default (21 from getRowHeight) and the text font size in the cell is increased, the value retrieved by getRowHeight is not changed from 21. Ref
  • There is also issue with wrapping text inside a cell which on my experience also causes errors in a pixel size of cell. Ref
  • From your question, when the selected cell range is large, the number of pages is more than 2. In this case, all pages cannot be correctly merged as an image.

From the above situation, I'm worried that obtaining the correct size of the selected cells might be difficult. So, I proposed to process this as image processing. Ref I thought that when this process is run with the image processing, the above issues might be able to be avoided.

But, unfortunately, in order to process this as image processing, there is no built-in method in Google Apps Script. But, fortunately, in your situation, it seems that Javascript can be used in a dialog. So, I created a Javascript library for achieving this process as the image processing. Ref

When this Javascript library is used, the sample demonstration is as follows.

enter image description here

Usage:

1. Prepare a Spreadsheet.

Please create a new Spreadsheet and put several values to the cells.

2. Sample script.

Please copy and paste the following script to the script editor of Spreadsheet.

Google Apps Script side: Code.gs

function getActiveRange_(ss, borderColor) {
  const space = 5;

  const sheet = ss.getActiveSheet();
  const range = sheet.getActiveRange();
  const obj = { startRow: range.getRow(), startCol: range.getColumn(), endRow: range.getLastRow(), endCol: range.getLastColumn() };
  const temp = sheet.copyTo(ss);
  const r = temp.getDataRange();
  r.copyTo(r, { contentsOnly: true });
  temp.insertRowAfter(obj.endRow).insertRowBefore(obj.startRow).insertColumnAfter(obj.endCol).insertColumnBefore(obj.startCol);
  obj.startRow += 1;
  obj.endRow += 1;
  obj.startCol += 1;
  obj.endCol += 1;
  temp.setRowHeight(obj.startRow - 1, space).setColumnWidth(obj.startCol - 1, space).setRowHeight(obj.endRow + 1, space).setColumnWidth(obj.endCol + 1, space);

  const maxRow = temp.getMaxRows();
  const maxCol = temp.getMaxColumns();
  if (obj.startRow + 1 < maxRow) {
    temp.deleteRows(obj.endRow + 2, maxRow - (obj.endRow + 1));
  }
  if (obj.startCol + 1 < maxCol) {
    temp.deleteColumns(obj.endCol + 2, maxCol - (obj.endCol + 1));
  }
  if (obj.startRow - 1 > 1) {
    temp.deleteRows(1, obj.startRow - 2);
  }
  if (obj.startCol - 1 > 1) {
    temp.deleteColumns(1, obj.startCol - 2);
  }

  const mRow = temp.getMaxRows();
  const mCol = temp.getMaxColumns();
  const clearRanges = [[1, 1, mRow], [1, obj.endCol, mRow], [1, 1, 1, mCol], [obj.endRow, 1, 1, mCol]];
  temp.getRangeList(clearRanges.map(r => temp.getRange(...r).getA1Notation())).clear();

  temp.getRange(1, 1, 1, mCol).setBorder(true, null, null, null, null, null, borderColor, SpreadsheetApp.BorderStyle.SOLID);
  temp.getRange(mRow, 1, 1, mCol).setBorder(null, null, true, null, null, null, borderColor, SpreadsheetApp.BorderStyle.SOLID);

  SpreadsheetApp.flush();
  return temp;
}

function getPDF_(ss, temp) {
  const url = ss.getUrl().replace(/\/edit.*$/, '')
    + '/export?exportFormat=pdf&format=pdf'
    // + '&size=20x20' // If you want to increase the size of one page, please use this. But, when the page size is increased, the process time becomes long. Please be careful about this.
    + '&scale=2'
    + '&top_margin=0.05'
    + '&bottom_margin=0'
    + '&left_margin=0.05'
    + '&right_margin=0'
    + '&sheetnames=false'
    + '&printtitle=false'
    + '&pagenum=UNDEFINED'
    + 'horizontal_alignment=LEFT'
    + '&gridlines=false'
    + "&fmcmd=12"
    + '&fzr=FALSE'
    + '&gid=' + temp.getSheetId();
  const res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
  return "data:application/pdf;base64," + Utilities.base64Encode(res.getContent());
}

// Please run this function.
function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const temp = getActiveRange_(ss, "#000000");
  const base64 = getPDF_(ss, temp);
  const htmltext = HtmlService.createTemplateFromFile('index').evaluate().getContent();
  htmltext = htmltext.replace(/IMPORT_PDF_URL/m, base64);
  const html = HtmlService.createTemplate(htmltext).evaluate().setSandboxMode(HtmlService.SandboxMode.NATIVE);
  SpreadsheetApp.getUi().showModalDialog(html, 'sample');
  ss.deleteSheet(temp);
}

function saveFile(data) {
  const blob = Utilities.newBlob(Utilities.base64Decode(data), MimeType.PNG, "sample.png");
  return DriveApp.createFile(blob).getId();
}

HTML & Javascript side: index.gs

Here, I used a Javascript library of CropImageByBorder_js for processing this as the image processing.

<script src="//mozilla.github.io/pdf.js/build/pdf.js"></script>
<script src="https://cdn.jsdelivr.net/gh/tanaikech/CropImageByBorder_js@latest/cropImageByBorder_js.min.js"></script>
<canvas id="canvas"></canvas>
<script>
  var pdfjsLib = window['pdfjs-dist/build/pdf'];
  pdfjsLib.GlobalWorkerOptions.workerSrc = '//mozilla.github.io/pdf.js/build/pdf.worker.js';
  const base64 = 'IMPORT_PDF_URL'; //Loaading the PDF from URL
  const cvs = document.getElementById("canvas");
  pdfjsLib.getDocument(base64).promise.then(pdf => {
    const {numPages} = pdf;
    if (numPages > 1) {
      throw new Error("Sorry. In the current stage, this sample script can be used for one page of PDF data. So, please change the selected range to smaller.")
    }
    pdf.getPage(1).then(page => {
      const viewport = page.getViewport({scale: 2});
      cvs.height = viewport.height;
      cvs.width = viewport.width;
      const ctx = cvs.getContext('2d');
      const renderContext = { canvasContext: ctx, viewport: viewport };
      page.render(renderContext).promise.then(async function() {
        const obj = { borderColor: "#000000", base64Data: cvs.toDataURL() };
        const base64 = await CropImageByBorder.getInnerImage(obj).catch(err => console.log(err));
        const img = new Image();
        img.src = base64;
        img.onload = function () {
          cvs.width = img.naturalWidth;
          cvs.height = img.naturalHeight;
          ctx.drawImage(img, 0, 0);
        }
        google.script.run.withSuccessHandler(id => console.log(id)).saveFile(base64.split(",").pop());
      });
    });
  });
</script>

3. Testing

When you test this script, please select the cells and run main(). By this, the selected cells are exported as an image (PNG) to the root folder as follows. In this case, you can see the above demonstration.

enter image description here

4. Flow.

In this sample script, the following flow is used.

  1. Manually select the cells, and run the script of main().
  2. At the script, the selected cells enclosed by the single row and column are created as a temporal sheet.
  3. Export the temporal sheet as a PDF data as base64. Here, the PDF data is sent to Javascript side.
  4. Convert 1st page of PDF data to an image using PDF.js.
  5. Cropping the selected cells using CropImageByBorder_js, and return the result image to Google Apps Script side.
  6. Save the image as a file to Google Drive.

LIMITATION:

  • In this sample script, it supposes that the selected range is put on one PDF page. So, when you select a large range, when the number of PDF pages is more than 2, unfortunately, this script cannot be used. So, please be careful about this.
  • And also, in this case, Javascript is used on a dialog. So, when you use this sample script, it is required to open the Spreadsheet and select the cells and run the script.

Note:

  • In your showing script, in order to use a created PDF data with PDF.js, the Spreadsheet is required to be publicly shared. But, in the case of PDF.js, it seems that the data URL can be directly used. So in this sample script, the created PDF is used as the data URL (base64). By this, it is not required to publicly share the Spreadsheet.

References:

Related