<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">I use Google Sheets rather than Excel for almost everything, and this inspired me to finally find how to do a basic script (custom function).</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">I've copied your spreadsheet to Google and added a column using a formula including the custom function. I'm not sure if the function itself will get shared properly when I share the document as below, so I will also paste the code of the function here.</div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><a href="https://docs.google.com/spreadsheets/d/1NG0ZH-gK537p84ilVPFOsGc8AepbDtYq3jFFHwqw-Vk/edit?usp=sharing">https://docs.google.com/spreadsheets/d/1NG0ZH-gK537p84ilVPFOsGc8AepbDtYq3jFFHwqw-Vk/edit?usp=sharing</a></font><br></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">I admit to having gotten some of the basic code for this from someone on the Internet, but had to change quite a bit to convert from Excel to Google, so I don't think I'm plagiarizing not to the cite the original.</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif">In Google, this goes into something in the Tools menu called Script Editor.</font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial, helvetica, sans-serif"><div class="gmail_default">function FindLastCharOccurence(fromText, fromTextLen, searchChar, searchCharLen)</div><div class="gmail_default">{</div><div class="gmail_default">var lastOccur = -1;</div><div class="gmail_default">var j = fromTextLen;</div><div class="gmail_default">var k = searchCharLen;</div><div class="gmail_default">for (var i=(j-k-1);i>=k;i--) </div><div class="gmail_default">{</div><div class="gmail_default"> if (fromText.substring(i, i+k) == searchChar)</div><div class="gmail_default"> {</div><div class="gmail_default"> lastOccur = i; </div><div class="gmail_default"> return lastOccur;</div><div class="gmail_default"> }</div><div class="gmail_default"> }</div><div class="gmail_default"> return lastOccur;</div><div class="gmail_default">}</div></font></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif;font-size:small">Melissa</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Mar 9, 2017 at 11:32 AM, Harker, Karen <span dir="ltr"><<a href="mailto:Karen.Harker@unt.edu" target="_blank">Karen.Harker@unt.edu</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="#0563C1" vlink="#954F72">
<div class="m_-2996276835497541121WordSection1">
<p class="MsoNormal">I’m wondering how others evaluate database usage data (DB1) from such meta-platforms as ProQUest & EBSCO, particularly searches. Attached is a DB1 report from PQ. I have two problems I haven’t been able to solve:<u></u><u></u></p>
<p class="m_-2996276835497541121MsoListParagraph"><u></u><span>1.<span style="font:7.0pt "Times New Roman"">
</span></span><u></u>Summarizing by product based on <b>Database</b>. The way they indicate the databases is hierarchical – Product->Collection-><wbr>Subcollection->Database. For example: SciTech Premium Collection->Materials Science & Engineering Database->Technology
Collection->Materials Business File. In addition to making some of these way too long for COUNTER-compliance (I’ve submitted a case to PQ support for this), I’m not sure how to summarize them. Sometimes, we purchase the individual database within a collection;
sometimes we purchased the whole collection. Sometimes, we’ve purchased perpetual access to parts of a product (e.g. Congressional Retrospective), and subscribed to another part (Congressional Prospective), but the names of these products aren’t in the
<b>Database</b> column. I need to see usage of the part that we subscribe to in order to determine if it’s still worth it to continue to subscribe.
<u></u><u></u></p>
<p class="m_-2996276835497541121MsoListParagraph"><u></u><span>2.<span style="font:7.0pt "Times New Roman"">
</span></span><u></u>The searches for the individual databases in a product are sometimes
<i>exactly</i> the same, and sometimes they are close, but not quite the same. I understand that this is due to their internal “federated” type searching, and so I use only Result Clicks & Record Views for evaluations. It’s just very frustrating it makes
summarizing the usage of these products more complicated. I’m trying to automate the process of not only collecting usage stats, but summarizing them for evaluation purposes, but there is so much data wrangling required.
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">So, how do you take the data provided by the DB1s for a product, say, the PQ Statistical Insight (which has many parts) or PQ Congressional (which has some parts that are purchased and some that are subscription), and summarize the data
in a way that makes sense?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Karen R. Harker, MLS, MPH<u></u><u></u></p>
<p class="MsoNormal">Collection Assessment Librarian<u></u><u></u></p>
<p class="MsoNormal">UNT Libraries, Denton, Texas<u></u><u></u></p>
<p class="MsoNormal"><a href="tel:(940)%20565-2688" value="+19405652688" target="_blank">940-565-2688</a><u></u><u></u></p>
<p class="MsoNormal"><a href="http://librariesareforuse.wordpress.com" target="_blank">http://librariesareforuse.<wbr>wordpress.com</a>
<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>______________________________<wbr>_________________<br>
Eril-l mailing list<br>
<a href="mailto:Eril-l@lists.eril-l.org">Eril-l@lists.eril-l.org</a><br>
<a href="http://lists.eril-l.org/listinfo.cgi/eril-l-eril-l.org" rel="noreferrer" target="_blank">http://lists.eril-l.org/<wbr>listinfo.cgi/eril-l-eril-l.org</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div>Melissa Belvadi</div><div>Collections Librarian</div><div>University of Prince Edward Island</div><div><a href="mailto:mbelvadi@upei.ca" target="_blank">mbelvadi@upei.ca</a> 902-566-0581</div><div><br></div><div><br></div></div></div>
</div>