Mengembalikan larik item yang cocok (yang telah diedit) dari dua larik lainnya dan item yang tidak cocok (yang belum diedit).

Tujuanku

Saya mencoba menautkan baris dalam dua lembar berbeda di spreadsheet yang sama sesuai dengan nilai dalam sel. Ide dasarnya adalah jika nilai di kolom B di Sheet1 memiliki nilai yang cocok di kolom B di Sheet2, hyperlink harus ditambahkan ke sel dengan nilai yang cocok di Sheet1 yang menghubungkan ke seluruh baris nilai yang cocok di Sheet2.

Apa yang saya lakukan

Seperti yang Anda lihat dari kode di bawah ini, ia mencari kecocokan, jika ditemukan, ia mengedit nilai yang cocok untuk menambahkan hyperlink. Saya tidak hanya ingin memasukkan nilai yang cocok "tertaut" ke dalam array baru, saya juga ingin nilai yang tidak cocok tanpa tautan. Idenya adalah bahwa tautan akan ditambahkan jika ditemukan kecocokan, jika tidak, nilainya akan tetap ditambahkan, hanya saja tanpa tautan.

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);
  }

Masalah

Kedua array masing-masing hanya berisi nilai unik. Masalahnya adalah, karena loop ganda, setiap item diperiksa berdasarkan panjang arrSheet2. Jadi misalnya mari kita bayangkan skenario ini:

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

arrOutput akan menghasilkan:

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

Dengan cara yang cepat, mungkin tidak elegan, untuk menyelesaikan masalah ini, saya telah mencoba menghapus duplikat dari arrOutput tetapi yang jelas nilai dengan tautan dan nilai tanpanya berbeda sehingga yang terbaik yang bisa didapat dengan solusi ini adalah ini:

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

Pertanyaan

Apakah ada cara yang lebih cerdas/efisien untuk mencapainya

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

atau seandainya apa yang saya lakukan benar-benar masuk akal, apa yang harus saya lakukan untuk mendapatkan hasil di atas?


google-sheets javascript google-apps-script
person res_cogitans    schedule 26.07.2019    source sumber

Jawaban (2)


arrow_upward
0
arrow_downward

Masalah:

  • Perulangan ganda dan mendorong elemen array beberapa kali

Larutan:

  • Gunakan pernyataan break dan kondisional if untuk mengontrol logika

Mengalir:

  • Jika hyperlink didorong, putuskan loop lembar kedua
  • Hanya dorong elemen Sheet1 jika tidak ada hyperlink di Sheet2 (misalnya, Tunggu hingga elemen terakhir Sheet2 diulang)

Potongan:

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]]);
    }
  }
}

Catatan:

  • Penggunaan objek {} mungkin lebih baik. Ubah array sheet2 menjadi objek {val1:hash,val2:hash,...}. Kemudian Anda dapat dengan mudah memeriksa apakah elemen sheet1 ada di sheet2 menggunakan in
person TheMaster    schedule 27.07.2019

arrow_upward
1
arrow_downward

Anda dapat menggunakan Array.prototype.filter untuk membuat array baru yang hanya berisi elemen yang cocok. Jika tidak ada elemen yang ditemukan, ini akan mengembalikan array kosong.

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

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

// ['apple','banana']

Atau tanpa polyfill Array.prototype.includes:

var intersect = arrSheet1.filter(function (element) {
  return arrSheet2.indexOf(element) !== -1);
})
person Cue    schedule 26.07.2019
comment
Fungsi panah belum didukung dalam skrip aplikasi - person TheMaster; 27.07.2019
comment
Terima kasih @TheMaster, diperbarui agar sesuai dengan komentar Anda. - person Cue; 27.07.2019
comment
Terima kasih atas jawaban Anda @Cue tetapi sayangnya ini tidak berhasil karena hasil yang saya cari bukan array yang hanya berisi elemen yang cocok. Seperti yang saya sebutkan, logikanya harus sebagai berikut: jika suatu elemen cocok maka tautan ditambahkan ke dalamnya (= HYPERLINK(elements)) dan didorong di arrOutput, jika tidak ada yang cocok maka elemen JUGA terdorong ke dalam array tetapi tanpa transformasi elemen itu sendiri dengan penambahan tautan. Oleh karena itu, array hasil yang saya tuju seharusnya adalah ['=HYPERLINK(apple)',avocado,'=HYPERLINK(banana)']. Saya harap ini lebih jelas sekarang! Terima kasih - person res_cogitans; 27.07.2019