修剪和清理Google脚本 [英] Trim and Clean Google script
问题描述
我可以使用以下脚本修剪我的数据
I can Trim my dada with the script below
但是有没有一种方法可以使用类似于VBA中清理的Google脚本来清理Google中的数据?
But is there a way Clean data in Google using a Google Script similar to Clean in VBA?
即删除所有非打印字符
我无法找到从其他来源复制并粘贴到GS中的数据
I am unable to find-replace on data copied and pasted into GS from another source
谢谢
function trimSpacesSHT(shtName) {
var sheet = SpreadsheetApp.getActive().getSheetByName(shtName);
var activeRange = sheet.getDataRange();
// Read Height and Width only once
var maxHeight = activeRange.getHeight();
var maxWidth = activeRange.getWidth();
// Read all values and formulas at once
var rangeValues = activeRange.getValues();
// iterate through all cells in the selected range
for (var cellRow = 0; cellRow < maxHeight; cellRow++) {
for (var cellColumn = 0; cellColumn < maxWidth; cellColumn++) {
rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim().replace(/\s(?=\s)/g,'');
}
}
// Write back all values at once
activeRange.setValues(rangeValues);
}
推荐答案
VBA的CLEAN方法删除了0-31, 127, 129, 141, 143, 144, 157
的字符.您想使用Google Apps脚本来实现吗?如果我的理解是正确的,那么该示例脚本如何?因为我对此方法感兴趣,并且想对此进行研究,所以我创建了它.
The CLEAN method of VBA removes the characters of 0-31, 127, 129, 141, 143, 144, 157
. You want to achieve this using Google Apps Script? If my understanding is correct, how about this sample script? Because I was interested in this method and want to study about this, I created this.
function cleanForGAS(str) {
if (typeof str == "string") {
var escaped = escape(str.trim());
for (var i = 0; i <= 31; i++) {
var s = i.toString(16);
var re = new RegExp("%" + (s.length == 1 ? "0" + s : s).toUpperCase(), "g");
escaped = escaped.replace(re, "");
}
var remove = ["%7F", "%81", "%8D", "%8F", "%90", "%9D"];
remove.forEach(function(e) {
var re = new RegExp(e, "g");
escaped = escaped.replace(re, "");
});
return unescape(escaped).trim();
} else {
return str;
}
}
用法:
使用此功能时,请修改脚本.
Usage :
When you use this, please modify your script.
rangeValues[cellRow][cellColumn] = rangeValues[cellRow][cellColumn].toString().trim().replace(/\s(?=\s)/g,'');
收件人:
rangeValues[cellRow][cellColumn] = cleanForGAS(rangeValues[cellRow][cellColumn].toString());
参考:
- WorksheetFunction.Clean方法( Excel)
- WorksheetFunction.Clean Method (Excel)
Reference :
如果我误解了你的问题,对不起.
If I misunderstand your question, I'm sorry.
这篇关于修剪和清理Google脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!