Google Spreadsheet Text Format Runs Extension for google-spreadsheet npm library

gistfile1.txt

This project extends the functionality of google-spreadsheet and adds the ability to control TextFormatRun

// Libraries
npm i lodash @types/lodash googleapis cheerio google-spreadsheet

// usage:
// getting cell from GoogleSpreadsheetWorksheet
cell.clearAllFormatting();
const text = `<b>lorem ipsum</b>`;
Object.assign(cell, htmlToTextFormatRuns(text));
// ...saveUpdatedCells();

Available HTML tags:
u, s, b, i, a, span

available "style" attribite with css params:
color <HEX string>
font-weight <bold>
font-style <italic>
font-size <String | Number>
text-decoration <underline|line-through>

Examples:
'<span style="font-size:8; color: #df9ee8">Ꮢ</span>'
`<a style="text-decoration: none; color: #ff0000" href="https://example.com">link text<a>`

Made in Ukraine 🇺🇦 with ❤️
https://github.com/lortonx

GoogleSpreadsheetAddon.ts

import * as cheerio from 'cheerio';
import { GoogleSpreadsheetCell } from 'google-spreadsheet';
import { sheets_v4 } from 'googleapis';
import _ from 'lodash';

declare module 'google-spreadsheet' {
    interface GoogleSpreadsheetCell {
        textFormatRuns: sheets_v4.Schema$TextFormatRun[];
    }
}

Object.defineProperty(GoogleSpreadsheetCell.prototype, 'textFormatRuns', {
    get(): sheets_v4.Schema$TextFormatRun[] {
        return this._draftData.textFormatRuns;
    },
    set(value: sheets_v4.Schema$TextFormatRun[]) {
        if (this['_draftData'].note === undefined) this._draftData.note = 'undefined';
        this._draftData.textFormatRuns = value;
    }
});

const orig_getUpdateRequest = GoogleSpreadsheetCell.prototype['_getUpdateRequest'];
GoogleSpreadsheetCell.prototype['_getUpdateRequest'] = function (this: GoogleSpreadsheetCell) {
    const isTextFormatRunsUpdated: boolean = this['_draftData'].textFormatRuns !== undefined;
    if (this['_draftData'].note == 'undefined') this['_draftData'].note = undefined;

    let res = orig_getUpdateRequest.call(this);
    if (res === null && isTextFormatRunsUpdated) {
        this.note = '';
        res = orig_getUpdateRequest.call(this);
    }

    if (isTextFormatRunsUpdated) {
        const cellValues = res.updateCells.rows[0].values[0] as sheets_v4.Schema$CellData;
        cellValues.textFormatRuns = this['_draftData'].textFormatRuns;
        res.updateCells.fields = [
            res.updateCells.fields,
            ..._.keys(
                _.pickBy({
                    textFormatRuns: isTextFormatRunsUpdated
                })
            )
        ].join(',');
    }
    return res;
};

export function htmlToTextFormatRuns(htmlText: string) {
    const $ = cheerio.load(htmlText);
    const runs: sheets_v4.Schema$TextFormatRun[] = [];
    let currentIndex = 0;

    const parsed = $('body');
    const value = parsed.text();
    function parseElement(elem: cheerio.AnyNode, parentStyle: sheets_v4.Schema$TextFormat) {
        const $elem = $(elem);
        if (elem.type === 'text') {
            const text = $elem.text();
            const endIndex = currentIndex + text.length;

            runs.push({
                startIndex: currentIndex,
                format: parentStyle
            } satisfies sheets_v4.Schema$TextFormatRun);
            currentIndex = endIndex;
        } else {
            const styles = $elem.css();

            const textStyle: sheets_v4.Schema$TextFormat = { ...parentStyle };
            if ($elem.is('u')) {
                textStyle.underline = true;
            } else if ($elem.is('s')) {
                textStyle.strikethrough = true;
            } else if ($elem.is('b')) {
                textStyle.bold = true;
            } else if ($elem.is('i')) {
                textStyle.italic = true;
            } else if ($elem.is('a')) {
                const href = $elem.attr('href');
                if (href)
                    textStyle.link = {
                        uri: href
                    };
            }

            for (const key in styles) {
                switch (key) {
                    case 'color':
                        const color = styles[key];
                        if (color.startsWith('#')) {
                            textStyle.foregroundColor = {
                                red: parseInt(color.slice(1, 3), 16) / 255,
                                green: parseInt(color.slice(3, 5), 16) / 255,
                                blue: parseInt(color.slice(5, 7), 16) / 255
                            };
                        }
                        break;
                    case 'font-weight':
                        if (styles[key] === 'bold') textStyle.bold = true;
                        break;
                    case 'font-style':
                        if (styles[key] === 'italic') textStyle.italic = true;
                        break;
                    case 'font-family':
                        textStyle.fontFamily = styles[key];
                        break;
                    case 'font-size':
                        textStyle.fontSize = parseFloat(styles[key]);
                        break;
                    case 'text-decoration':
                        if (styles[key] === 'underline') {
                            textStyle.underline = true;
                        } else if (styles[key] === 'line-through') {
                            textStyle.strikethrough = true;
                        } else {
                            textStyle.underline = false;
                            textStyle.strikethrough = false;
                        }
                        break;
                }
            }

            $elem.contents().each((i, el) => parseElement(el, textStyle));
        }
    }
    $('body')
        .contents()
        .each((i, el) => parseElement(el, {}));
    return { value: value, textFormatRuns: runs };
}

export function toA1Notation(start_y_col: number, start_x_row: number, end_y_col: number, end_x_row: number) {
    const startColLetter = columnToLetter(start_y_col + 1);
    const endColLetter = columnToLetter(end_y_col + 1);
    // Учитывая, что индексы строк в Google Sheets начинаются с 1, не нужно преобразование для номера строки.
    return `${startColLetter}${start_x_row + 1}:${endColLetter}${end_x_row + 1}`;
}
export function columnToLetter(column: number) {
    let temp: number,
        letter = '';
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}

export const colToLetter = (index: number, a = 0) =>
    (a = Math.floor(index / 26)) >= 0 ? colToLetter(a - 1) + String.fromCharCode(65 + (index % 26)) : ''; // Ref: https://stackoverflow.com/a/53678158