Sort of VLookup脚本删除列中的所有数据(Appscript, Gsheet)

Question

下午好，

我目前正在开发谷歌表上的数据库，我正试图使用一个类似于VLookup的脚本来填充来自另一个GSheet的数据

我用脚本从这个评论:https://stackoverflow.com/a/60266697/19427703

我面临的问题是，当我运行我的脚本，所有的数据(即使数据不匹配)被擦除。我希望我的脚本在ID不匹配时将数据留在单元格中。

我很感激你的帮助，

提前谢谢你，这是我的代码:

const ss = SpreadsheetApp.getActive();
/**
* @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
* @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
* @param {Number} fromCompCol -Column number of fromSht to compare
* @param {Number} toCompCol -Column number of toSht to compare
* @param {Number} fromCol -Column number of fromSht to get result
* @param {Number} toCol -Column number of toSht to get result
*/
function Refresh(
fromSht = ss.getSheetByName('Sheet1'),
toSht = ss.getSheetByName('Sheet2'),
fromCompCol = 2,
toCompCol = 2,
fromCol = 1,
toCol = 1
) {
const toShtLr = toSht.getLastRow();
const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
const fromArr = fromSht.getDataRange().getValues();
fromCompCol--;
fromCol--;
/*Create a hash object of fromSheet*/
const obj1 = fromArr.reduce((obj, row) => {
let el = row[fromCompCol];
el in obj ? null : (obj[el] = row[fromCol]);
return obj;
}, {});
//Paste to column
toSht
.getRange(2, toCol, toShtLr - 1, 1)
.setValues(toCompArr.map(row => (row[0] in obj1 ? [obj1[row[0]]] : [row[toCol]])));
}

这是我的输入和结果(假设对于6个矩阵，范围是A1:B4)

表1:

ID101105110

Answer 1

null在这里设置，如果row[0]不在哈希对象中:

setvalue (toCompArr。地图(行=比;(row[0] in obj1 ?[obj1[row[0]]]: [null])));}

将列设置为toColRng，并将其值设置为null:

const ss = SpreadsheetApp.getActive();
/**
* @param {GoogleAppsScript.Spreadsheet.Sheet} fromSht -Sheet to import from
* @param {GoogleAppsScript.Spreadsheet.Sheet} toSht -Sheet to import to
* @param {Number} fromCompCol -Column number of fromSht to compare
* @param {Number} toCompCol -Column number of toSht to compare
* @param {Number} fromCol -Column number of fromSht to get result
* @param {Number} toCol -Column number of toSht to get result
* @author TheMaster https://stackoverflow.com/users/8404453
*/
function vlookup_3(
fromSht = ss.getSheetByName('Sheet1'),
toSht = ss.getSheetByName('Sheet2'),
fromCompCol = 1,
toCompCol = 1,
fromCol = 2,
toCol = 2
) {
const toShtLr = toSht.getLastRow();
const toCompArr = toSht.getRange(2, toCompCol, toShtLr - 1, 1).getValues();
const toColRng = toSht.getRange(2, toCol, toShtLr - 1, 1);
const toColArr = toColRng.getValues();
const fromArr = fromSht.getDataRange().getValues();
fromCompCol--;
fromCol--;
/*Create a hash object of fromSheet*/
const obj = fromArr.reduce((obj, row) => {
let el = row[fromCompCol];
el in obj ? null : (obj[el] = row[fromCol]);
return obj;
}, {});
//Paste to column
toColRng.setValues(
toCompArr.map((row, i) => (row[0] in obj ? [obj[row[0]]] : toColArr[i]))
);
}