如何处理 RichTextValues() 中的空值
- 我已经研究这段代码几天了。我开始只是在活动工作表上构建每月日历,这不可避免地导致我希望将我的事件放置在它们上,这最终导致我想要添加富文本以更好地处理较小字体大小的附加文本的格式。
但是,最近我开始收到此错误:
Unexpected error while getting the method or property setRichTextValues on object SpreadsheetApp.Range
代码.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();//used with richtext
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;
}
但这是有问题的部分;我将二维值数组转换为富文本值。
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;
}
只是好奇那些一直使用富文本的人是否有更好的方法来处理空单元格的问题。该解决方案只是在空单元格中放置一个空格并继续前进。
这是当前日历的样子。我无法将整个季度都穿上。
它可能会更干净,但我同意。说到外表,我没那么挑剔。
我应该采取卡洛斯·M 所采取的更简单的道路。希望接下来我会考虑这个问题,但我是这样做的:
当前工作解决方案:
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;
}