Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-api

Resolve script error in google app script for sheets "The parameters (number[],String,String) don't match the method signature for Utilities.newBlob."

I'm trying to convert a column containing several rows of HTML code into rich text in another column. When I do it on a single cell, I succeed. But when I try to convert all the rows in the column into another column, I get the following error message "Exception: The parameters (number[],String,String) don't match the method signature for Utilities.newBlob. convertHTMLToRichText @ richtext.gs:12"

Could you help me make my code work? I should point out that I'm really (really) newbie.

function convertHTMLToRichText() {
  var ss = SpreadsheetApp.openById("18AvwS9scxVxix7CgPhr2UGjH7qts21j8xv47xInY-hw"); // Please set the Spreadsheet ID.
  var sheet = ss.getSheets()[5];
  var shData = ss.getSheetByName('test');
  
  var searchValue = shData.getRange('G2').getValue();

  // 1. Retrieve HTML from a cell.
  var html = sheet.getRange("G2:G"+ shData.getLastRow()).getValues();

  // 2. Create Google Document by converting HTML to Google Document as a temporal file.
  var blob = Utilities.newBlob(html, MimeType.HTML, "sample.html");
  var tempDocId = Drive.Files.insert(
    { title: "temp", mimeType: MimeType.GOOGLE_DOCS },
    blob
  ).id;

  // 3. Put the value to a cell as the rich text using the method of "DocumentToSpreadsheet".
  var res = RichTextApp.DocumentToSpreadsheet({
    range: sheet.getRange("D2:D"),
    document: DocumentApp.openById(tempDocId),
  });
  console.log(res);

  // 4. Remove the temporal file.
  DriveApp.getFileById(tempDocId).setTrashed(true);
}

The first code I tested only worked for one cell and not several, so I modified the code using other code snippets found on stackoverflow, but it doesn't work.

Solution

  • In your script, how about the following modification?

    Modified script 1:

    function convertHTMLToRichText() {
  var ss = SpreadsheetApp.openById("18AvwS9scxVxix7CgPhr2UGjH7qts21j8xv47xInY-hw"); // Please set the Spreadsheet ID.
  var sheet = ss.getSheetByName('test');

  // 1. Retrieve HTML from a cell.
  var values = sheet.getRange("G2:G" + sheet.getLastRow()).getValues();

  // 2. Create Google Document by converting HTML to Google Document as a temporal file.
  values.forEach((r, i) => {
    r.forEach((html, j) => {
      var blob = Utilities.newBlob(html, MimeType.HTML, "sample.html");
      var tempDocId = Drive.Files.insert({ title: "temp", mimeType: MimeType.GOOGLE_DOCS }, blob).id;

      // 3. Put the value to a cell as the rich text using the method of "DocumentToSpreadsheet".
      var res = RichTextApp.DocumentToSpreadsheet({
        range: sheet.getRange(2 + i, 4 + j),
        document: DocumentApp.openById(tempDocId),
      });
      console.log(res);

      // 4. Remove the temporal file.
      DriveApp.getFileById(tempDocId).setTrashed(true);
    });
  });
}

    • When this script is run, the HTML data of "G2:G" is put into cells "D2:D" as the rich text by rendering.

    • In your script, you are using var html = sheet.getRange("G2:G"+ shData.getLastRow()).getValues();. In this case, sheet and shData are used. From your script, I modified it to var values = sheet.getRange("G2:G" + sheet.getLastRow()).getValues(). In this case, var sheet = ss.getSheetByName('test'); is used. Please be careful about this.

    Modified script 2:

    Unfortunately, from your question, I do not know your HTML data. So, when your HTML data in the cells "G2:G" is the document including no line breaks, the following modification might be able to be also used. For example, when your HTML data in the cells "G2:G" is the document including the line breaks, please use the above modification. Because, in the case of pasteData of Sheets API, when your HTML data has line breaks, the rendered values are put into multiple cells. Please be careful about this.

    When you use this script, please enable Sheets API at Advanced Google services.

    function convertHTMLToRichText() {
  const ss = SpreadsheetApp.openById("18AvwS9scxVxix7CgPhr2UGjH7qts21j8xv47xInY-hw");
  const sheet = ss.getSheetByName('test');
  const sheetId = sheet.getSheetId();
  const range = sheet.getRange("G2:G3");
  const values = range.getValues();
  const requests = values.map(([g], i) => ({ pasteData: { html: true, data: g, coordinate: { sheetId, rowIndex: 2 + i, columnIndex: 3 } } }));
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}
    • When this script is run, the HTML data of "G2:G" is put into cells "D2:D" as the rich text by rendering.

    Note:

    • About the reason for your current error message of Exception: The parameters (number[],String,String) don't match the method signature for Utilities.newBlob. convertHTMLToRichText @ richtext.gs:12, I think that Tedinoz's comment is useful. Ref

    References: