我正在尝试使用 Google 脚本和 Google 电子表格检测 B 列中的值是否已在 A 列中提及。这些列位于 2 个单独的工作表中。由于某种原因,我没有足够的经验来让它工作:)提前谢谢
获取北欧
function insert(e,sheet) {
var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/12NpQZBCaw2jmK5oR-zWmvfNxGeMjiAQzB4-RFtX30W4/edit#gid=0");
var sheet = ss.getSheetByName("Sheet1")"
var check2 =sheet.getRange(seclastrow, 1).getValue();
var st = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1gYEedTcsbjNKLl5Fmd1kxROIah5uIq3VHG_O7JKvEs0/edit#gid=0");
var sheett = st.getSheetByName("sheet1");
var data = sheett.getRange("P8:P50").getValues();
var newData = new Array();
for (i in data) {
var row = data[i];
/* TODO feature enhancement in de-duplication
var date_modified =row[row.length-2];
var order_key = row[row.length];
var existingDataSearchParam = order_key + "/" + date_modified;
*/
var duplicate = false;
for (j in newData) {
var rowNewData = newData[j];
var new_order_key = rowNewData[rowNewData.length];
//var newDataSearchParam = new_order_key + "/" + new_date_modified; // TODO feature enhancement in de-duplication
if(check2 == newData) {
duplicate = true;
}
// TODO feature enhancement in de-duplication
/*if (existingDataSearchParam == newDataSearchParam){
duplicate = true;
}*/
}
if (duplicate == true) {
sheett.getRange(8, 26).setValue("Success");;
}else {
sheett.getRange(8, 26).setValue("Not Found");
}
}
var truefalse = sheett.getRange(8, 26).getValue();
return ContentService
.createTextOutput(newData)
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
查找两个不同电子表格中不同列之间的重复项
您需要提供 ID0、ID1、SheetName0 和 SheetName1
function findDuplicatesInDifferentSpreadsheets() {
var ss0=SpreadsheetApp.OpenById("ID0");
var sh0=ss0.getSheetByName("SheetName0");
var rg0=sh0.getRange(1,1,sh0.getLastRow(),1);
var vA0=rg0.getValues();
var ss1=SpreadsheetApp.openById('ID1');
var sh1=ss1.getSheetByName("SheetName1");
var rg1=sh1.getRange(1,2,sh1.getLastRow(),1);
var vA1=rg1.getValues();
var dA=[];//duplicate array
var v0A=vA0.map(function(r){return r[0];});//flatten
var v1A=vA1.map(function(r){return r[0];});//flatten
for(var i=0;i<v0A.length;i++) {
for(var j=0;j<v1A.length;j++) {
if(v0A[i]==v1A[j]) {
dA.push(Utilities.formatString('Duplicate Found:<br />SpreadSheet: %s SheetName: %s Row: %s Value: %s<br />SpreadSheet: %s SheetName: %s Row: %s Value: %s',ss0.getName(),sh0.getName(),i+1,v0A[i],ss1.getName(),sh1.getName(),j+1,v1A[j]));
}
}
}
//Logger.log(dA);
var html=dA.join('<br />');
var userInterface=HtmlService.createHtmlOutput(html);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Duplicates');//Results Dialog
}
我的数据表
- Google Apps 脚本文档 https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
- JavaScript 参考 https://developer.mozilla.org/en-US/docs/Web/JavaScript/New_in_JavaScript/1.6
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)