Search code examples
google-apps-scriptgoogle-sheetsrtf

Google Apps Script: Read rtf value from API and paste as formatted text into Spreadsheet

I am using Google Apps Script to fetch rich text in the following form:

{"blocks":[{"key":"dgn8a","text":"This Text is rich.","type":"unstyled","depth":0,"inlineStyleRanges":[{"offset":0,"length":4,"style":"ITALIC"},{"offset":13,"length":4,"style":"ITALIC"},{"offset":0,"length":4,"style":"BOLD"},{"offset":5,"length":4,"style":"UNDERLINE"}],"entityRanges":[],"data":{}}],"entityMap":{}}

and I would like to paste this text in the correct format to a Google sheet.

Can anybody help?

Thanks in advance.

Regards Lars

Solution

  • I believe your goal is as follows.

    • You have the metadata object of converted rich texts with draft-js.
    • You want to put your object to cells of the Spreadsheet as the rich text.

    In this case, how about the following sample script?

    Sample script:

    function myFunction() {
  // This is from your question.
  const obj = { "blocks": [{ "key": "dgn8a", "text": "This Text is rich.", "type": "unstyled", "depth": 0, "inlineStyleRanges": [{ "offset": 0, "length": 4, "style": "ITALIC" }, { "offset": 13, "length": 4, "style": "ITALIC" }, { "offset": 0, "length": 4, "style": "BOLD" }, { "offset": 5, "length": 4, "style": "UNDERLINE" }], "entityRanges": [], "data": {} }], "entityMap": {} };

  // Convert from your object to rich text for a Spreadsheet.
  const convStyles = { "BOLD": "setBold", "ITALIC": "setItalic", "UNDERLINE": "setUnderline" };
  const richTextValues = obj.blocks.map(({ inlineStyleRanges, text }) => {
    const temp = SpreadsheetApp.newRichTextValue().setText(text);
    inlineStyleRanges.forEach(({ offset, length, style }) =>
      temp.setTextStyle(offset, offset + length, SpreadsheetApp.newTextStyle()[convStyles[style]](true).build())
    );
    return [temp.build()];
  });

  // Put rich text to the cells.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  sheet.getRange(1, 1, richTextValues.length).setRichTextValues(richTextValues);
}

    Testing:

    When the above script is run, the following result is obtained.

    enter image description here

    Note:

    • This sample script is a simple sample script for understanding the script and converting your provided object to rich text for the Spreadsheet. Unfortunately, I cannot know your actual situation. So, when you want to use other styles, please modify the above script.

    References: