Tips & Tricks

Legacy Catalog
1. View MARC in Legacy Catalog
Archives Portal
1. View XML in Archives Portal
Metadata Management System
1. View MODS XML
Google Sheets
OpenRefine
1. Fetching Data from VIAF
  1. Fetching Redirected VIAF Identifiers
  2. Fetching Library of Congress Identifiers from VIAF
See Also

Legacy Catalog

View MARC in Legacy Catalog

Place an x in front of a record URL, e.g.: https://legacycatalog.nypl.org/xrecord=b21530562~S1

Archives Portal

View XML in Archives Portal

Add .xml after a collection URL, e.g.: https://archives.nypl.org/brg/19343.xml

Only EAD-encoded finding aids will have XML that contains the full finding aid’s content.

Metadata Management System

View MODS XML

On the Descriptive metadata tab of a Collection, Container, or Item record, use either method:
1. Click Preview complete MODS record under the Descriptive Metadata Heading
2. Click XML MODS export
  1. In the popup prompt, type admin as the username and password as the password

Google Sheets

Division Acronym → MSU Liaison

Replace A2 with the cell containing the division’s acronym

=IFERROR(vlookup(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1P-YDJigon640fTCLP4Ig4-zmzqrX88v5M24ShuxFNVY/edit#gid=0","Metadata Liaisons and Contacts!A3:F37"),6,false),)

Sierra Location Code → Location Name

Replace A2 with the cell containing the Sierra location code

=IFERROR(vlookup(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1E8Dbd8M5OotQfGaKjL5cLKlyex5g8D1KBeZeArJtcuU/edit#gid=1867405302","sierra-codes-locations!F2:G2889"),2,false))

Full bnumber → Short bnumber

Replace both instances of A2 with the cell containing the full bnumber

=LEFT(A2,(SUM(LEN(A2)-1)))

Library of Congress Identifier → Library of Congress Label

Replace A2 with the cell containing the Library of Congress identifier

=INDEX(IMPORTXML("http://id.loc.gov/authorities/names/"&A2, "//meta[@name='dc.subject']/@content"), 2)

Import List of Relator Name Elements

Use this function to import relator names into a standalone tab in your spreadsheet for data validation

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1lG2UypYYLTSefUWsTv2w8qLfm6tP-gflJ7VWr3QZIM0/edit#gid=0","name element!A2:A268")

Replace A2 with the cell containing the URL

=IF(REGEXMATCH(A2, "digital.nypl.org|digilib.nypl.org|llink.nypl.org|digitalgallery.nypl.org|nypl.org/digital|link.nypl.org|purl.nypl.org|digitalcollections.nypl.org"), TRUE, FALSE)

Add Thumbnail Using Image ID

Replace A2 with the cell containing the Image ID

=IMAGE(https://images.nypl.org/index.php?t=w&id=A2)

Copy Cell Above If Cell Is Blank

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Count Occurrences in Row

Replace A$2:A with the range and A2 with the cell containing the string you wish to count

=COUNTIF(A$2:A,A2)

Highlight Duplicate Cell Data in Row

Select the column you’d like to highlight duplicate cell data
From the Format menu, select Conditional Formatting and the Conditional format rules menu will open on the right side of the window
In the Format cells if… dropdown menu, select Custom formula is and paste the below formula in the Value or Formula field, replacing the three occurrences of A with the letter of the column that you’d like to highlight duplicate cell data, and click the Done button to apply

=COUNTIF(A:A,A2)>1

Make Cells with URLs Clickable

Navigate to Edit › Find and replace
Enter http in both the Find and Replace with fields
Click Replace all twice

OpenRefine

Fetching Data from VIAF

Prepare a column in OpenRefine containing VIAF identifiers
In the header of a column containing VIAF identifiers, navigate to ▼ › Add column by fetching URLs…
In the prompt, enter a New column name, paste the below expression in the Expression field, and click the OK button
```
 "http://viaf.org/viaf/" + value + "/viaf.json"
```
Allow time for OpenRefine to fetch the JSON data
1. Progress will be indicated by a message at the top of the screen

Fetching Redirected VIAF Identifiers

Follow the steps to fetch data from VIAF
In the header of a column containing VIAF JSON, navigate to ▼ › Add column based on this column…
In the prompt, enter a New column name, paste the below expression in the Expression field, and click the OK button
```
 value.parseJson().redirect.directto
```
Optional: To facet your data to display only rows with redirects:
1. In the header of the newly created column, navigate to ▼ › Facet › Customized facets › Facet by blank (null or empty string)
2. In the Facet / Filter tab in the left column of OpenRefine, find the facet corresponding to the newly created column and select false

Fetching Library of Congress Identifiers from VIAF

Follow the steps to fetch data from VIAF
In the header of a column containing VIAF JSON, navigate to ▼ › Add column based on this column…

In the prompt, enter a New column name, paste the below expression in the Expression field, and click the OK button

 value.parseJson().sources.source.find(/"LC\|([^"]+)"/)[0].replace("\"", "").split("|")[1].replace(" ", "")

Optional: To facet your data to display only rows with Library of Congress identifiers:
1. In the header of the newly created column, navigate to ▼ › Facet › Customized facets › Facet by blank (null or empty string)
2. In the Facet / Filter tab in the left column of OpenRefine, find the facet corresponding to the newly created column and select false

This same technique can be used to retrieve additional identifiers from VIAF by replacing LC with the code that corresponds to the respective authority source code.