Unexpected error while getting the method or property setRichTextValues on object SpreadsheetApp.Ran-CodePudding

How to Handle Null Values in RichTextValues()

I've been working on this code for a couple of days. I started with just building a monthly calendar on the active sheet which inevitably lead me to want my events placed on them which ultimately lead me to want to add rich text to better handle the formatting of the additional text in a smaller font size.

However, recently I started getting this error:

Unexpected error while getting the method or property setRichTextValues on object SpreadsheetApp.Range

This is the whole code:

Codes.gs:

function monthlyCalendarWithEvents(obj) {
  var m = obj.m || new Date().getMonth();
  var wsd = obj.wsd || 1;//defaults to Monday
  const calids = obj.calids || CalendarApp.getAllOwnedCalendars().map(c => c.getId());
  const cals = calids.map(id => CalendarApp.getCalendarById(id));
  const td = new Date();
  const [cy, cm, cd] = [td.getFullYear(), td.getMonth(), td.getDate()];
  const dA = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
  const oA = [...Array.from(Array(7).keys(), idx => dA[(idx   wsd) % 7])]
  let dObj = {};
  let midx = {};
  let rObj = { cA: null, roff: null, coff: null };
  oA.forEach(function (e, i) { dObj[e] = i; });
  const mA = [...Array.from(new Array(12).keys(), x => Utilities.formatDate(new Date(2021, x, 15), Session.getScriptTimeZone(), "MMM"))];
  mA.forEach((e, i) => { midx[i] = i; })
  let cA = [];
  let bA = [];
  let wA = [null, null, null, null, null, null, null];
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clear();
  const year = new Date().getFullYear();
  let i = midx[m % 12];
  let month = new Date(year, i, 1).getMonth();
  let dates = new Date(year, i   1, 0).getDate();
  var events = { pA: [] };
  cals.forEach(c => {
    let evs = c.getEvents(new Date(year, month, 1), new Date(year, month, dates));
    evs.forEach(ev => {
      let st = ev.getStartTime();
      let dd = st.getDate();
      let hh = st.getHours();
      let mm = st.getMinutes();
      let sts = `${hh}:${mm}`;
      if (!events.hasOwnProperty(dd)) {
        events[dd] = [];
        events[dd].push(`${ev.getTitle()} - ${sts}`);
        events.pA.push(dd);
      } else {
        events[dd].push(`${ev.getTitle()} - ${sts}`);
      }
    });
  });
  cA.push([mA[month], dates, '', '', '', '', '']);
  bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
  cA.push(oA)
  //bA.push(['#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00']);
  let d = [];
  let ddd = [];
  for (let j = 0; j < dates; j  ) {
    let day = new Date(year, i, j   1).getDay();
    let date = new Date(year, i, j   1).getDate();
    if (day < wA.length) {
      wA[dObj[dA[day]]] = date;
      if (events.hasOwnProperty(date)) {
        wA[dObj[dA[day]]]  = '\n'   events[date].join('\n')
      }
    }
    if (cy == year && cm == month && cd == date) {
      rObj.roff = cA.length;
      rObj.coff = dObj[dA[day]];
    }
    if (dA[day] == oA[wA.length - 1] || date == dates) {
      cA.push(wA);
      //bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
      wA = ['', '', '', '', '', '', ''];
    }
  }
  const dtnotcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('black').build();
  const dtcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('red').build();
  const evsty = SpreadsheetApp.newTextStyle().setFontSize(6).setForegroundColor('black').build();
  rObj.cA = cA;
  rObj.crtA = cA.map((r, i) => {
    let row = [];
    r.map((c, j) => {
      if (c == '' || c == null) {
        row.push(null);
        return;
        //c = ' ';//heres the current solution
      }
      if(typeof c != 'string') {
        c = c.toString();
      }
      let idx = c.indexOf('\n');
      let rtv = SpreadsheetApp.newRichTextValue().setText(c);
      if (rObj.roff == i && rObj.coff == j) {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          rtv.setTextStyle(0, c.length, dtcur);
          row.push(rtv.build());
        }
      } else {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtnotcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          if (c.length > 0) {
            rtv.setTextStyle(0, c.length, dtnotcur);
            row.push(rtv.build());
          } else {
            row.push(rtv.build());
          }
        }
      }
    });
    return row;
  });
  return rObj;
}

But this is the section that has the problem; where I'm converting the 2d values array to richtextvalues.

const dtnotcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('black').build();
  const dtcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('red').build();
  const evsty = SpreadsheetApp.newTextStyle().setFontSize(6).setForegroundColor('black').build();
  rObj.cA = cA;
  rObj.crtA = cA.map((r, i) => {
    let row = [];
    r.map((c, j) => {
      if (c == '' || c == null) {//I started by pushing a null into the row array and skipping to  the next loop but that's when I started get the error
        row.push(null);
        return;
        //c = ' ';//heres the current solution
      }
      if(typeof c != 'string') {
        c = c.toString();
      }
      let idx = c.indexOf('\n');//the inital error was cannot find function indexOf() of null
      let rtv = SpreadsheetApp.newRichTextValue().setText(c);
      if (rObj.roff == i && rObj.coff == j) {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          rtv.setTextStyle(0, c.length, dtcur);
          row.push(rtv.build());
        }
      } else {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtnotcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          if (c.length > 0) {
            rtv.setTextStyle(0, c.length, dtnotcur);
            row.push(rtv.build());
          } else {
            row.push(rtv.build());
          }
        }
      }
    });
    return row;
  });
  return rObj;
}

Just curious if those of you that have been working with rich text have a better way of handling the issue of empty cell. This solution simply places a space in the empty cell and moves on.

Here's what the current calendar looks like. I couldn't fit the entire quarter on.

It could probably be cleaner but I'm okay with it. I'm not that finnicky when it comes to looks.

I should have taken the simpler path that Carlos M took. Hopefully I'll think about that next but here's was I did:

Current Working Solution:

function monthlyCalendarWithEvents(obj) {
  var m = obj.m || new Date().getMonth();
  var wsd = obj.wsd || 1;//defaults to Monday
  const calids = obj.calids || CalendarApp.getAllOwnedCalendars().map(c => c.getId());
  const cals = calids.map(id => CalendarApp.getCalendarById(id));
  const td = new Date();
  const [cy, cm, cd] = [td.getFullYear(), td.getMonth(), td.getDate()];
  const dA = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];
  const oA = [...Array.from(Array(7).keys(), idx => dA[(idx   wsd) % 7])]
  let dObj = {};
  let midx = {};
  let rObj = { cA: null, roff: null, coff: null };
  oA.forEach(function (e, i) { dObj[e] = i; });
  const mA = [...Array.from(new Array(12).keys(), x => Utilities.formatDate(new Date(2021, x, 15), Session.getScriptTimeZone(), "MMM"))];
  mA.forEach((e, i) => { midx[i] = i; })
  let cA = [];
  let bA = [];
  let wA = [null, null, null, null, null, null, null];
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  sh.clear();//after clearing the sheet I get the rtvnull which I then push into the final output whenever I hit an empty cell.
  const rtvnull = sh.getRange("A1").getRichTextValue();
  const year = new Date(new Date().getFullYear(),m,1).getFullYear();
  let i = midx[m % 12];
  let month = new Date(year, i, 1).getMonth();
  let ldom = new Date(year, i   1, 0).getDate();
  var events = { pA: [] };
  cals.forEach(c => {
    let evs = c.getEvents(new Date(year, month, 1), new Date(year, month, ldom));
    evs.forEach(ev => {
      let st = ev.getStartTime();
      let dd = st.getDate();
      let hh = st.getHours();
      let mm = st.getMinutes();
      let sts = `${hh}:${mm}`;
      if (!events.hasOwnProperty(dd)) {
        events[dd] = [];
        events[dd].push(`${ev.getTitle()} - ${sts}`);
        events.pA.push(dd);
      } else {
        events[dd].push(`${ev.getTitle()} - ${sts}`);
      }
    });
  });
  cA.push([mA[month], ldom, '', '', '', '', '']);
  bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
  cA.push(oA)
  //bA.push(['#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00', '#ffff00']);
  let d = [];
  let ddd = [];
  for (let j = 0; j < ldom; j  ) {
    let day = new Date(year, i, j   1).getDay();
    let date = new Date(year, i, j   1).getDate();
    if (day < wA.length) {
      wA[dObj[dA[day]]] = date;
      if (events.hasOwnProperty(date)) {
        wA[dObj[dA[day]]]  = '\n'   events[date].join('\n')
      }
    }
    if (cy == year && cm == month && cd == date) {
      rObj.roff = cA.length;
      rObj.coff = dObj[dA[day]];
    }
    if (dA[day] == oA[wA.length - 1] || date == ldom) {
      cA.push(wA);
      //bA.push(['#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff', '#ffffff']);
      wA = ['', '', '', '', '', '', ''];
    }
  }
  const dtnotcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('black').build();
  const dtcur = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('red').build();
  const evsty = SpreadsheetApp.newTextStyle().setFontSize(6).setForegroundColor('black').build();
  rObj.cA = cA;
  rObj.crtA = cA.map((r, i) => {
    let row = [];
    r.map((c, j) => {
      if (c == '' || c == null) {
        row.push(rtvnull);//this is where I push the rtvnull into thus no longer needing to put a space in and have to run through the rest of the loop.
        return;
        //c = ' ';//here is the old space solution in case the other one fails in the near future for some other yet unforeseen problem
      }
      if(typeof c != 'string') {
        c = c.toString();
      }
      let idx = c.indexOf('\n');
      let rtv = SpreadsheetApp.newRichTextValue().setText(c);
      if (rObj.roff == i && rObj.coff == j) {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          rtv.setTextStyle(0, c.length, dtcur);
          row.push(rtv.build());
        }
      } else {
        if (~idx) {
          rtv.setTextStyle(0, idx, dtnotcur)
          rtv.setTextStyle(idx   1, c.length, evsty);
          row.push(rtv.build());
        } else {
          if (c.length > 0) {
            rtv.setTextStyle(0, c.length, dtnotcur);
            row.push(rtv.build());
          } else {
            row.push(rtv.build());
          }
        }
      }
    });
    return row;
  });
  return rObj;
}

CodePudding user response：

Explanation:

When you use getRichTextValues() on an empty range (or cell), you will not get null, instead it still outputs a RichTextValue object. So it's actually illegal to set null to a range using setRichTextValues():

This sample script was ran on an empty spreadsheet:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getRange("A1:A2");
  var arr = range.getRichTextValues();
  Logger.log(arr);
  var bold = SpreadsheetApp.newTextStyle()
    .setBold(true)
    .build();
  var richTextA1 = SpreadsheetApp.newRichTextValue()
    .setText("This cell is bold")
    .setTextStyle(bold)
    .build();
  range.setRichTextValues([[richTextA1],null]);
}

Instead of null, you can push the RichTextValue object returned by an empty cell as a placeholder.

Reference:

Class Range | getRichTextValues()