CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
kiseijuu001
kiseijuu001

Reputation: 3

Need guidance on creating an Apps script or Sheets formula to match sub-sheet names to a list of unique IDs

More detailed information here. I have a list of 16 workbooks with a list of sub-sheets in them organized by last name, numbering anywhere from 15-100 names per workbook. Each person has a unique ID number that needs to be placed in their respective sub-sheet. I have a separate file with the names and IDs that need to be inserted. I need to create either a script or a sheets formula that:

Iterates over all the sub-sheets, gets the sheet name, finds the name and ID in the other file, inserts the ID into a cell in the correct sub-sheet and then moves on to the next sheet in the workbook. I am very new to Sheets and scripting so I am quite lost. There is a compounding issue as well:

Since I have a list of all the IDs and people I need this is not a huge deal as the script can just match based on the last name and I can manually change the duplicates.

Here is an anonymized sheet with sample names and IDs

Here is an example sheet with last names as sub-sheets

Any help is appreciated, thanks.

Updated with table for Names and IDs sheet:

First Name Last Name ID
Dave Smith 247
Jack Smith 248
Jane Doe 143
Evelyn Borca 1292
Cherie Tenny 1148
Brent Brooks 285

Screenshot of workbook with sub-sheets that needs ids inserted based on last name match - https://i.ibb.co/Ydpqvyn/workbook-example.jpg

Upvotes: 0

Views: 68

Answers (1)

Cooper
Cooper

Reputation: 64140

function loademplidsintheresheets() {
  const niss = SpreadsheetApp.openById('nissid');
  const sh = niss.getSheetByName('Names and Ids');//spreadsheet with names and id
  const vs = sh.getDataRange().getDisplayValues();
  let ids = { pA: [] };//ids object
  let cnt = { pA: [] };//names count object
  vs.forEach(r => {
    if(!cnt.hasOwnProperty(r[1])) {
      cnt[r[1]]=1;
      cnt.pA.push(r[1]);
    } else {
      cnt[r[1]] += 1;
    }
  });
  vs.forEach(r => {
    if(!ids.hasOwnProperty(r[1])) {
      if(cnt[r[1]] > 1) {
        ids[`${r[0].charAt(0)}_${r[1]}`] = r[2];//if cnt > 1
      } else {
        ids[r[1]] = r[2];//in last name is unique
      }
    }
  });
  const ss = SpreadsheetApp.getActive();//assume spreadsheet ids are in the active spreadsheet
  const wsh = ss.getSheetByName('Spreadsheeds and Ids');
  const [hA,...wbs] = wsh.getDataRange().getDisplayValues();
  const idx ={};
  hA.forEach((h,i) => idx[h]=i);
  wbs.forEach(r =>{
    let twb = SpreadsheetApp.openById(r[idx['id header']]);
    twb.getSheets().forEach(s => {
      s.getRange('A1Notation for the cell you want the id in').setValue(ids[sh.getName()]);
    });
  })
}

Upvotes: 0

Related Questions