Is there a way to extract data from user info pop-up on Google Sheets?

Question

first ever question here!

1. I have a Google Sheets with a bunch of gmail addresses in one column
2. When I hover over the gmail address I get a little pop-up with the profile photo
3. If I right-click and inspect that pop-up element, I can find a variable called "data-person-id"
4. The ID is a 21-digit Google User ID
5. What I would love is a way to automatically extract that User ID into the second column, so I have the User ID for each gmail address, tabulated

Any pointers at all would be amazing as I don't really know where to start!

Thank you so much

Answer 1

From your question, I guessed that you might have wanted to retrieve the value of the profile ID of PersonMetadata. If my understanding is correct, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and set your sheet name, and set the column letter of the column that the email addresses are put.

And, in this sample, People API is used. So, please enable People API at Advanced Google services.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const column = "A"; // Please set the column letter that emails are put.

  // Retrieve profile IDs of metadata of People API as an object.
  const obj1 = People.OtherContacts.list({ readMask: "emailAddresses,metadata", pageSize: 1000, sources: ["READ_SOURCE_TYPE_PROFILE", "READ_SOURCE_TYPE_CONTACT"] }).otherContacts.flatMap(({ emailAddresses, metadata: { sources } }) => {
    const temp = sources.find(({ type }) => type == "PROFILE");
    const id = temp ? temp.id : "";
    return emailAddresses.map(({ value }) => [value, id]);
  });
  const obj2 = People.People.Connections.list("people/me", { personFields: "emailAddresses,metadata", pageSize: 1000, sources: ["READ_SOURCE_TYPE_PROFILE", "READ_SOURCE_TYPE_CONTACT"] })
    .connections.flatMap(({ emailAddresses, metadata: { sources } }) => {
      const temp = sources.find(({ type }) => type == "PROFILE");
      const id = temp ? temp.id : "";
      return emailAddresses.map(({ value }) => [value, id]);
    });
  const obj = { ...Object.fromEntries(obj1), ...Object.fromEntries(obj2) };

  // Retrieve email addresses from a column, and put the profile IDs to the right side of the column.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(`${column}1:${column}${sheet.getLastRow()}`);
  const values = range.getDisplayValues().map(([r]) => [r ? (obj[r] || null) : null]);
  range.offset(0, 1).setValues(values);
}

• In this sample script, it supposes that the email addresses are put into the column "A" of "Sheet1". And, when this script is run, the email addresses are retrieved from column "A" of "Sheet1", and an object including the email and the profile ID of PersonMetadata is created. And, the profile IDs of PersonMetadata are put to the right side of the column (In this case, it's column "B".) using the object.

• If the row that the empty value is put means that the profile ID cannot be retrieved.

Note:

• If the profile IDs of PersonMetadata cannot be retrieved by "Method: otherContacts.list" and "Method: people.connections.list" and cannot be retrieved, the empty values are put. And also, the service account has no profile ID. Please be careful about this.
• In your situation, I'm worried that your goal might not be able to be achieved by the built-in functions.

References:

• Method: people.connections.list
• Method: otherContacts.list