How to save formatted text from google sheet cells to .rtf with Google App Script?

Question

I have a simple script in Google App Script, which takes string-values from cells in a google sheet (RSS-Feeds) and saves (updates) some .rtf - Documents. After some sync-processes and macro-helpers, those texts are displayed on a scrolling LED-bar, which can be updated through WIFI.

This works but I didn't manage to format the texts in the .rtf - Documents.

I experimented with the RichTextValueBuilder, which works fine to add formatting to text in a google spreadsheet but wasn't successful for formatting text in the .rtf's... Another experiment was with html-tags. This brought the desired formatting-effect in the rtf-file but the application for the led-bar couldn't handle it.

Does anybody know how to help a noob in this situation? Thanks a lot in advance!

function dataLED() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ass = ss.getSheetByName("Newsticker");

//VALUES FROM CELLS IN GOOGLE SHEETS
  var valuesLedBar = ass.getRange(2, 10, 4, 1).getValues();
  var vak1 = valuesLedBar[0];
  var vak2 = valuesLedBar[1];
  var vak3 = valuesLedBar[2];
  var vak4 = valuesLedBar[3]; 
  var marge = ass.getRange("B16").getValue();

//SAVE IN .RTF-FILES (IN FOLDER dataLEDbar)
  var folders = DriveApp.getFoldersByName("dataLEDbar");
  
  if (folders.hasNext()) {
    var folder = folders.next();

    saveData(folder, "0808003135589.rtf", vak1);
    saveData(folder, "0808003133343.rtf", vak2);
    saveData(folder, "0808003136619.rtf", vak3);
    saveData(folder, "0808003135964.rtf", vak4);
    saveData(folder, "0808182631849.rtf", "Gewinnmarge diesen Monat:" + marge);
  }
  else {null}

};


//-------------------------------------------------SAVE

function saveData(folder, fileName, contents) {

  var children = folder.getFilesByName(fileName);
  var file = null;
  if (children.hasNext()) {
    file = children.next();
    file.setContent(contents);
  } else {
    file = folder.createFile(fileName, contents);
  }
}

Answer 1

Here is my understanding:

• You have a Google Spreadsheet which has the rich text in the cells of "J2:J5".
• You want to retrieve the rich texts from the cells of "J2:J5", and want to create them as each file with the mimeType of application/rtf.
  • In your case, 4 rich text files are created.
• You want to achieve this using Google Apps Script.

In this answer, in order to achieve your goal, I used a Google Apps Script library of RichTextApp.

Usage:

1. Install GAS library.

Please install the GAS library of RichTextApp. You can see how to install at here.

2. Sample script.

Please copy and paste the following sample script, and run myFunction. By this, when the folder is existing, 4 rich text files are created to the folder.

function myFunction() {
  const filenames = ["0808003135589.rtf", "0808003133343.rtf", "0808003136619.rtf", "0808003135964.rtf"];
  const folderName = "dataLEDbar";
  const sheetName = "Newsticker";

  const folders = DriveApp.getFoldersByName(folderName);
  const folder = folders.hasNext() ? folders.next() : DriveApp.getRootFolder();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  for (let i = 2; i <= 5; i++) {
    const range = sheet.getRange("J" + i);
    const doc = DocumentApp.create("tempDocument");
    const docId = doc.getId();
    RichTextApp.SpreadsheetToDocument({range: range, document: doc});
    doc.saveAndClose();
    const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=rtf&id=" + docId;
    const res = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}});
    folder.createFile(res.getBlob().setName(filenames[i - 2]));
    DriveApp.getFileById(docId).setTrashed(true);
  }
}

Note:

• This is a simple sample script. So please modify this for your actual situation.

Reference:

• RichTextApp