我有一个脚本来追加唯一记录。但是,当我运行脚本时,它将带有超链接的文件名转换为简单文本(删除超链接)。将记录从一个选项卡追加到另一个选项卡时,如何保留超链接的文件名?
附加唯一记录应用脚本:
function appendUniqueRows() {
var ss = SpreadsheetApp.getActive();
var sourceSheet = ss.getSheetByName('Tab 1');
var destSheet = ss.getSheetByName('Tab 2');
var sourceData = sourceSheet.getRange('W5:AC5').getValues();
var destData = destSheet.getDataRange().getValues();
// Check whether destination sheet is empty
if (destData.length === 1 && "" === destData[0].join('')) {
// Empty, so ignore the phantom row
destData = [];
}
// Generate hash for comparisons
var destHash = {};
destData.forEach(function(row) {
destHash[row.join('')] = true; // could be anything
});
// Concatentate source rows to dest rows if they satisfy a uniqueness filter
var mergedData = destData.concat(sourceData.filter(function (row) {
var hashedRow = row.join('');
if (!destHash.hasOwnProperty(hashedRow)) {
// This row is unique
destHash[hashedRow] = true; // Add to hash for future comparisons
return true; // filter -> true
}
return false; // not unique, filter -> false
}));
// Check whether two data sets were the same width
var sourceWidth = (sourceData.length > 0) ? sourceData[0].length : 0;
var destWidth = (destData.length > 0) ? destData[0].length : 0;
if (sourceWidth !== destWidth) {
// Pad out all columns for the new row
var mergedWidth = Math.max(sourceWidth,destWidth);
for (var row=0; row<mergedData.length; row++) {
for (var col=mergedData[row].length; col<mergedWidth; col++)
mergedData[row].push('');
}
}
// Write merged data to destination sheet
destSheet.getRange(1, 1, mergedData.length, mergedData[0].length)
.setValues(mergedData);
}
例如:
function copyTextWithLink() {
file = SpreadsheetApp.getActive();
sheet1 = file.getSheetByName("Sheet1");
sheet2 = file.getSheetByName("Sheet2");
range1 = sheet1.getDataRange();
values1 = range1.getRichTextValues();
let output = values1.map((row) => {
return row.map((cell) => {
let newCell = SpreadsheetApp.newRichTextValue()
.setText(cell.getText())
.setLinkUrl(cell.getLinkUrl())
.build();
return newCell;
});
});
let height = output.length
let width = output[0].length
let outputRange = sheet2.getRange(1,1,height,width)
outputRange.setRichTextValues(output)
}
getRichTextValues
它们。getValues
除了返回RichTextValue
对象外,其他功能与之相同。setRichTextValues
其复制到所有数据,包括将超链接复制到目标。包含此步骤是为了说明如何分别从富文本值数组中获取和设置文本或链接。RichTextValueBuilder
:let newCell = SpreadsheetApp.newRichTextValue()
.setText(cell.getText())
.setLinkUrl(cell.getLinkUrl())
.build();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句