ส่งคืนอาร์เรย์ของรายการที่ตรงกัน (แก้ไขแล้ว) ของอาร์เรย์อื่นอีก 2 รายการและรายการที่ไม่ตรงกัน (ไม่ได้แก้ไข)

เป้าหมายของฉัน

ฉันกำลังพยายามเชื่อมโยงแถวในสองแผ่นงานที่แตกต่างกันในสเปรดชีตเดียวกันตามค่าในเซลล์ แนวคิดพื้นฐานคือหากค่าในคอลัมน์ B ใน Sheet1 มีค่าที่ตรงกันในคอลัมน์ B ใน Sheet2 ควรเพิ่มไฮเปอร์ลิงก์ลงในเซลล์โดยมีค่าที่ตรงกันใน Sheet1 ซึ่งเชื่อมโยงกับแถวทั้งหมดของค่าที่ตรงกันใน Sheet2

ฉันทำอะไรลงไป

ดังที่คุณเห็นจากโค้ดด้านล่าง มันจะค้นหารายการที่ตรงกัน หากพบ มันจะแก้ไขค่าที่ตรงกันเพื่อเพิ่มไฮเปอร์ลิงก์ ฉันไม่เพียงต้องการส่งค่าที่ตรงกันที่ "เชื่อมโยง" ลงในอาร์เรย์ใหม่เท่านั้น ฉันยังต้องการค่าที่ไม่ตรงกันโดยไม่มีลิงก์ด้วย แนวคิดก็คือว่าลิงก์จะถูกเพิ่มหากพบรายการที่ตรงกัน ไม่เช่นนั้นค่าจะยังคงถูกเพิ่มเข้าไป เพียงแต่ไม่มีลิงก์เท่านั้น

function linkToContacts(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var Sheet1 = ss.getSheetByName("Sheet1");
    var Sheet2 = ss.getSheetByName("Sheet2");
    var Sheet2ID = Sheet2.getSheetId();

    var arrSheet1 = Sheet1.getRange(4,2,Sheet1.getLastRow()-3).getValues(); // -3 because I have 3 row as headers before the data, which start in row 4 col B
    var arrSheet2 = Sheet2.getRange(4,2,Sheet2.getLastRow()-3).getValues(); // -3 because I have 3 row as headers before the data, which start in row 4 col B

    var arrOutput = [];

    for(var i = 0; i<arrSheet1.length;i++){
      for(var j = 0; j<arrSheet2.length;j++) {
        if(arrSheet1[i][0] === arrSheet2[j][0]){
          arrOutput.push(['=HYPERLINK("#gid=' + Sheet2ID + '&range=' + Sheet2.getRange(j+4,2,1,Sheet2.getLastColumn()-1).getA1Notation() + '";"' + arrSheet1[i][0] + '")']);
        } else {
          arrOutput.push([arrSheet1[i][0]]);
        }
      }
    }

    Sheet1.getRange(4,2,Sheet1.getLastRow()).clearContent();
    Sheet1.getRange(4,2,arrOutput.length).setValues(arrOutput);
  }

ปัญหา

ทั้งสองอาร์เรย์มีเพียงค่าที่ไม่ซ้ำตามลำดับ ปัญหาคือว่า เนื่องจากการวนซ้ำสองครั้ง แต่ละรายการจะถูกตรวจสอบตามความยาวของ arrSheet2 ตัวอย่างเช่น ลองจินตนาการถึงสถานการณ์นี้:

var arrSheet1 = [apple,avocado,banana];
var arrSheet2 = [apple,banana,mango,];

arrOutput จะส่งผลให้:

arrOutput = [
          apple(link),apple,apple,
          avocado,avocado,avocado,
          banana(link),banana,banana
];

ด้วยวิธีที่รวดเร็วและไม่สง่างามในการแก้ปัญหาที่ฉันได้พยายามลบรายการที่ซ้ำกัน frm arrOutput แต่เห็นได้ชัดว่าค่าที่มีลิงก์และค่าที่ไม่มีแตกต่างกันดังนั้นวิธีที่ดีที่สุดที่จะได้รับจากโซลูชันนี้คือ:

arrOutput = [
          apple(link),apple,
          avocado,
          banana(link),banana
];

คำถาม

มีวิธีที่ชาญฉลาดกว่า/มีประสิทธิภาพมากกว่าในการไปที่นั่นหรือไม่

arrOutput = [apple(link),avocado,banana(link)];

หรือในกรณีที่สิ่งที่ฉันทำอยู่สมเหตุสมผลจริงๆ ฉันควรทำอย่างไรเพื่อให้ได้ผลลัพธ์ข้างต้น


google-sheets javascript google-apps-script
person res_cogitans    schedule 26.07.2019    source แหล่งที่มา

คำตอบ (2)


arrow_upward
0
arrow_downward

ปัญหา:

  • การวนซ้ำสองครั้งและการพุชองค์ประกอบอาร์เรย์หลายครั้ง

สารละลาย:

  • ใช้คำสั่ง break และเงื่อนไข if เพื่อควบคุมตรรกะ

ไหล:

  • ถ้าไฮเปอร์ลิงก์ถูกผลัก ให้แยกแผ่นงานที่สองออก
  • กดองค์ประกอบ Sheet1 เท่านั้นหากไม่มีไฮเปอร์ลิงก์ใน Sheet2 (เช่น รอจนกระทั่งองค์ประกอบสุดท้ายของ Sheet2 วนซ้ำ)

ตัวอย่าง:

for(var i = 0; i<arrSheet1.length;i++){
  for(var j = 0, k = arrSheet2.length-1; j<=k; j++) {// modified
    if(arrSheet1[i][0] === arrSheet2[j][0]){
      arrOutput.push(['=HYPERLINK("#gid=' + Sheet2ID + '&range=' + Sheet2.getRange(j+4,2,1,Sheet2.getLastColumn()-1).getA1Notation() + '";"' + arrSheet1[i][0] + '")']);
      break; //break j loop to continue next i loop
    } else if(j === k){//push sheet1 only on the last element
      arrOutput.push([arrSheet1[i][0]]);
    }
  }
}

บันทึก:

  • การใช้วัตถุ {} อาจจะดีกว่า แปลงอาร์เรย์ sheet2 เป็นวัตถุ {val1:hash,val2:hash,...} จากนั้นคุณสามารถตรวจสอบได้อย่างง่ายดายว่ามีองค์ประกอบ sheet1 อยู่ในแผ่นงาน 2 หรือไม่โดยใช้ in
person TheMaster    schedule 27.07.2019

arrow_upward
1
arrow_downward

คุณสามารถใช้ Array.prototype.filter เพื่อสร้าง อาร์เรย์ใหม่ที่มีเฉพาะองค์ประกอบที่ตรงกันเท่านั้น หากไม่พบองค์ประกอบ ระบบจะส่งคืนอาร์เรย์ว่าง

var arrSheet1 = ['apple','avocado','banana'];
var arrSheet2 = ['apple','banana','mango'];

var intersect = arrSheet1.filter(function (element) {
  return arrSheet2.includes(element);
});

// ['apple','banana']

หรือไม่มี Array.prototype.includes polyfill:

var intersect = arrSheet1.filter(function (element) {
  return arrSheet2.indexOf(element) !== -1);
})
person Cue    schedule 26.07.2019
comment
ฟังก์ชันลูกศรยังไม่รองรับในสคริปต์ของแอป - person TheMaster; 27.07.2019
comment
ขอบคุณ @TheMaster อัปเดตเพื่อให้สอดคล้องกับความคิดเห็นของคุณ - person Cue; 27.07.2019
comment
ขอบคุณสำหรับคำตอบของคุณ @Cue แต่น่าเสียดายที่สิ่งนี้ใช้ไม่ได้เนื่องจากผลลัพธ์ที่ฉันกำลังมองหาไม่ใช่อาร์เรย์ขององค์ประกอบที่ตรงกันเท่านั้น ดังที่ฉันได้กล่าวไว้ ตรรกะควรเป็นไปตามนั้น: หากองค์ประกอบตรงกัน ลิงก์จะถูกเพิ่มเข้าไป (=HYPERLINK(องค์ประกอบ)) และมันถูกพุชใน arrOutput หากไม่มีรายการที่ตรงกัน องค์ประกอบนั้นก็จะถูกผลักใน อาร์เรย์ แต่ไม่มีการเปลี่ยนแปลงองค์ประกอบด้วยการเพิ่มลิงก์ ดังนั้นอาร์เรย์ผลลัพธ์ที่ฉันมุ่งหมายควรเป็น ['=HYPERLINK(apple)',avocado,'=HYPERLINK(banana)'] ฉันหวังว่ามันจะชัดเจนมากขึ้นตอนนี้! ขอบคุณ - person res_cogitans; 27.07.2019