A spreadsheet is a very powerful tool for organizing any kind of data in an adequate manner. It's been used widely in accomplishing tasks in various fields, like education, business, and research data analysis or statistical analysis. A simple search (allintitle: spreadsheet) was conducted on Google Scholar to understand the varied practices of spreadsheets during 1990-2020. More than 4 thousand titles were found exceptionally. It straightforwardly indicates how popular tool this has been for long decades. Within the library and information science domain, it also has been utilized mostly by researchers and professionals in analysing and organizing data (e.g. metrics studies or cleaning data). Though, several statistical packages also are getting used extensively (e.g R, SPSS, SAS, Stata, SciPy).
There is a number of functions available in spreadsheets (e.g MS excel or Google sheets). Here, an attempt has been made to demonstrate a search (bibliographic data) function in Google sheet. There are no such high programming skills involved in this demonstration. Few spreadsheet functions rather will be applied. We have to consider some important aspects. First, this is only for those who use a spreadsheet for managing bibliographic data in the form of an accession register and other records. Second, this is based on an exact search process. The steps are:
#Step 1: Creating an accession register or having an existing one
This is an example of an accession register or record (Sheet1). It is somewhat arranged in MARC 21 data entry format. The remaining fields (columns) are Pagination (J), General Note (K), Subject Headings (L), Accession/Barcode no. (M), and Call number (N). We may have more fields. Now, we can rename Sheet1 to "Data".
#Step 2: Pulling all the columns to display for search result
Next, we need to add a worksheet.
We can name it "Selected fields". In that case, we will use the query function (one of the powerful functions) to pull selected columns from the "Data" worksheet to the "Selected fields" worksheet. The query function is
=QUERY(Data!A1:N11,"select D, E, F, G, H, I, J, L,M,N, A,B",1)
In the cell A1 of the Selected fields worksheet, we put the query function which incorporates the data range (Data !A1:N50), query ("select D, E, F, G, H, I, J, L, M, N, A, B"), and 1 for our headers. The clauses are like SQL. But some differences are there in this query language. For example, we do not get to apply FROM clause. Furthermore, the clauses aren't case sensitive. We can see that the selected columns are taken for display purposes. This is often what it gives after querying the above function:
Only 12 columns/ fields have been pulled and this will be displayed as a search result (based on specific search).
#Step 3: Pulling selected data (columns) for searching
Again, we are going to add another worksheet where we will pull all our data by that we can make a search. In addition, we will make a dropdown list to the subsequent worksheet that we are also going to add after this one.
=QUERY('Selected fields'!A1:L11, "select A, B, E, H")
Instead of copying and pasting, we will use the query function or can create arrays (curly brackets/{}) to get/pull our data (only column A, B, E, and H from the Selected fields worksheet). It is a good practice to use the same data as formatted. We will rename Sheet3 to "Dropdown List". The main purpose of adding this worksheet is to show it in the search option. In other words, the entire data from column A-D will be displayed during any search. We can add more fields though. For now, we can proceed with only 4 fields (Title, Statement of responsibility, Publisher, and Subject headings) that will be sufficient for searching. If we've more than one subject heading, we will combine them using semicolons.
#Step 4: Data validation
We have to add our final worksheet where we will apply a search function. Then we will be all set to do further tasks. This is our "Sheet 4" and that we can change it by naming "Search". we will format as per our own choices and wish. For now, I'm going to format it like this. Here, we'll be selecting Data>Data validation. Cell no. F2:H2 (merged) has been selected for validation. Next, we will validate by following:
We select our cell range (1), the criteria (2) will be List from a range, our absolute references (3) will be "Dropdown list" sheet, and checking the "Show dropdown list in cell" (4). Now, we will save this.
We can design the worksheet for beautification. We also can see that a list has come up containing the data of title, author, publisher, and subject headings.
#Step 5: Applying query function for search results
In the worksheet "Search", we manually can add headers or can leave them for the query. For now, I have added the headers manually. Next, in the cell A5, we can put in the following query:
=IFERROR(QUERY('Selected fields'!A2:L," SELECT * WHERE (A = """&F2&""") OR (B = """&F2&""") OR (E = """&F2&""") OR (H = """&F2&""")",0))
Here is that the formula. First, an IFERROR function has been applied to avoid errors if arise. Second, we select the data range from the "Selected fields" worksheet, because it was mentioned earlier that this worksheet is going to be working as display search results. Third, we'd like to use two clauses (Select and Where). We don't need to mention all the columns one by one, rather we'll use * to return all the columns of the worksheet. Fourth, the WHERE clause is required to return the rows that match a specified condition. Fifth, we need to specify our condition. For instance, whatever we type or select from the dropdown list (F2) are going to be matched to column A of the worksheet the Selected fields. Sixth, we use to OR logical operator to match multiple columns from the same worksheet. As we selected 4 different search criteria. Hence, we will get results of 4 different criteria. Alongside the dropdown list, we can also type our queries and therefore the list will be coming. We may append strings using """&F2&""". It means, whatever we type within the cell F2 (merged from F2 to H2) are going to be returned as result. Seventh, need to omit the headers, as we manually added headers within the Search worksheet. So, we'll use 0. Here is that the outcome.
If we type computer within the cell, all the computer-related records are going to be displayed(can be title or subject heading). Hopefully, it can be useful for retrieving data. Though, modern libraries use integrated library systems or discovery systems that make information retrieval much easier. Still, a spreadsheet is undoubtedly a powerful one and therefore the beauty of it is infinite.