Question 248000 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – I have to create a script that will find a common value between sheets and then copy data from 3 cells in that row to 3 cells in the related sheet

I need a script for google sheets. My worksheet has 2 sheets called SOURCE and TARGET.

The TARGET sheet has the following columns and ranges: BO_BENE_ID range A2 – A2392, BENE_BIRTH_DT range E2 – E2392, BENE_FIRST_NAME range F2 – F2392, BENE_LAST_NAME range G2 – G2392.

The SOURCE sheet has the following columns and ranges: BO_BENE_ID range A2 – A106761, BENE_BIRTH_DT range J2 – J106761, BENE_FIRST_NAME range L2 – L106761, BENE_LAST_NAME range N2 – N106761.

When a BO_BENE_ID from TARGET range A2 – A2392 is equal to a cell from SOURCE range A2 – A106761 the script should copy the BENE_BIRTH_DT, BENE_FIRST_NAME, and BENE_LAST_NAME that from that row in SOURCE sheet and copy it to BENE_BIRTH_DT, BENE_FIRST_NAME, and BENE_LAST_NAME in the row with the matching BO_BENE_ID in the TARGET sheet.

I am very new at this and have been trying to stitch something together from other posts but I can’t get it to work. Any guidance would be hugely appreciated. Thank you

I have tried looking at other scripts that search for a value and replace a cell but I have been unable to get the desired outcome. I have tried getting an AI developed script based on my desired outcome but that was nowhere near what I needed to do.

Tags:

2

Answers


  1. 0

    Try this:

    function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName("Sheet0");//source
  const ssr = 2;
  const tsh = ss.getSheetByName("Sheet1");//target
  const tsr = 2;
  const svs = ssh.getRange(ssr,1,ssh.getLastRow() - ssr + 1,ssh.getLastColumn()).getValues();
  const tvs = tsh.getRange(tsr, 1, tsh.getLastRow() - tsr + 1,tsh.getLastColumn()).getValues();
  const tcol1 = tvs.map(r => r[0]);
  svs.forEach((r,i) => {
    let idx = tcol1.indexOf(r[0]);
    if(~idx && !tvs[idx][1] && !tvs[idx][2] && !tvs[idx][3]){
      tsh.getRange(idx + 2, 2, 1, 3).setValues([r.slice(1)]);
    }
  });
}

    Source Sheet:

    BO_BENE_ID range A2 – A2392 BENE_BIRTH_DT range E2 – E2392 BENE_FIRST_NAME range F2 – F2392 BENE_LAST_NAME range G2 – G2392.
    1 1/1/2023 FirstName1 LastName1
    2 1/2/2023 FirstName2 LastName2
    3 1/3/2023 FirstName3 LastName3
    4 1/4/2023 FirstName4 LastName4
    5 1/5/2023 FirstName5 LastName5
    6 1/6/2023 FirstName6 LastName6
    7 1/7/2023 FirstName7 LastName7
    8 1/8/2023 FirstName8 LastName8
    9 1/9/2023 FirstName9 LastName9
    10 1/10/2023 FirstName10 LastName10

    Target Sheet:

    Before:

    BO_BENE_ID range A2 – A106761 BENE_BIRTH_DT range J2 – J106761 BENE_FIRST_NAME range L2 – L106761 BENE_LAST_NAME range N2 – N106761
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    After:

    BO_BENE_ID range A2 – A106761 BENE_BIRTH_DT range J2 – J106761 BENE_FIRST_NAME range L2 – L106761 BENE_LAST_NAME range N2 – N106761
    1 1/1/2023 FirstName1 LastName1
    2 1/2/2023 FirstName2 LastName2
    3 1/3/2023 FirstName3 LastName3
    4 1/4/2023 FirstName4 LastName4
    5 1/5/2023 FirstName5 LastName5
    6 1/6/2023 FirstName6 LastName6
    7 1/7/2023 FirstName7 LastName7
    8 1/8/2023 FirstName8 LastName8
    9 1/9/2023 FirstName9 LastName9
    10 1/10/2023 FirstName10 LastName10
    Login or Signup to reply.
  2. 0

    Although I’m not sure whether I could correctly understand your question, how about the following sample script?

    In this sample script, from your question, it has already known the target columns of "SOURCE" and "TARGET" sheets. So, I used this situation.

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet. And, please confirm the sheet names and target columns of each sheet in obj.

    function myFunction() {
  const srcSheetName = "SOURCE"; // This is from your question.
  const dstSheetName = "TARGET"; // This is from your question.
  const obj = { src: [1, 10, 12, 14], dst: [1, 5, 6, 7] }; // This is from your question.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));
  const srcValues = srcSheet.getRange("A2:N" + srcSheet.getLastRow()).getValues().reduce((m, r) => m.set(r[obj.src[0] - 1], obj.src.slice(1).map(e => r[e - 1])), new Map());
  const dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow() - 1, dstSheet.getLastColumn());
  let dstValues = dstRange.getValues().map(r => {
    const v = srcValues.get(r[obj.src[0] - 1]);
    if (v) {
      obj.dst.slice(1).map((e, i) => r[e - 1] = v[i]);
    }
    return r;
  });
  dstRange.setValues(dstValues);
}

    Testing:

    When this script is run, the following sample situation is obtained.

    enter image description here

    Note:

    • In this sample script, it supposes the following situation.

      • All values of BO_BENE_ID are unique values in "SOURCE" sheet. There are no duplicate values.
      • Columns "E" to "F" of "TARGET" sheet are overwritten by new values from "SOURCE" sheet.

    References:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search