Import Cell Data Into Another Sheet In Google Calendar

I’m not sure if this is the 100% best way to handle this, but it is the method that finally worked for me. I have one Google Sheet which has multiple sheets (tabs). In the overview tab, I want to pull in data from the other sheets which may change. I don’t want to worry about constantly keeping it updated, so I wanted to automatically pull that data in. This is the formula that worked for me:

=ImportRange(“REPLACEME”,”2018!D41″)

Step One
Replace in this instance refers to the URL of your spreadsheet. Look to the top and copy everything after https://docs.google.com/spreadsheets/d/, but stop before you get to /edit#. Take that string of characters and put it in the formula instead of the phrase REPLACEME.

Step Two
The 2018 in my formula is actually the name of the sheet that I’m pulling from. Replace 2018 with your tab’s name that you want pull data from. Note: you may need to add underscores if there are spaces.

Step Three
Replace D41 with the cell that you actually want to call in.

If there are better, faster methods (because that first import is slow), leave a comment and share!

Substituting Multiple Strings in Airtable

Thanks to W_Vann_Hall over on the Airtable Forums, I was able to figure out the formula I needed. The task is this:

* In JotForm, people select book covers to indicate which book they like.
* A Zapier zap takes that submission and puts it in my Airtable.
* However, the data appears at a bad URL with https://jotform.com added to the front of it. Worthless for our purposes.
* So to change that info, I need to substitute the long junk URL to the name of the book.
* I created a new column and added this formula:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Name of Column — leave in brackets},’https://www.ilovemylibrary.com/images/little_red.jpg’,’Little Red Riding Hood, ‘),’https://www.ilovemylibrary.com/images/three_little_pigs.jpg’,’Three Little Pigs, ‘),’https://www.ilovemylibrary.com/images/cinderella.jpg’,’Cinderella, ‘)

* Output: Little Red Riding Hood, Three Little Pigs, Cinderella,
* Note that I added a comma and a space at the end of each item. I’m sure I could try to figure out how to check for any leftover items, so there isn’t an extra comma, but eh.

I’m sure there’s more work to be done and something will break, but I’m grateful to get at least this much figured out.

Google Campaign URL Builder

I’ve been having trouble with getting my URL campaigns to show up consistently in Google Analytics. Fortunately, after talking over on some forums, they pointed me in the right direction. Though only two fields are required when using the Campaign URL Builder, you’ll want to feel out the first four fields at minimum.

Tips

I recommend keeping a spreadsheet to track all your campaign names and to develop consistent naming habits for your source and medium fields. For our end of year fundraising email, I made the mistake of using end_of_year_fundraising and eoy_fundraising as campaign names. If I had used the same term, the results would look nicer in Google Analytics.

For the campaign that worked, I get the campaign name of lnap_2018. When I click on it, then I see all the sources listed. It was helpful in knowing immediately that most people were clicking through to the registration form from the front of the website.

Bitly Integration

I should note that we have a (free!) custom URL through Bitly.com for our URL shortener. I grab a URL, take it to the Campaign URL Builder and add in my elements. Then I copy the generated URL into Bitly to give me a unique URL. Then when I look at my report later, I can see clicks from eblast, social media (should break this down to platform), website, etc.

Prepopulate JotForm Fields via URL

We use JotForm for our library’s website. It’s fairly easy to customize, their customer service forums are fast, and they provide lots of integrations.

One feature I had been musing on was how to pre-fill a field on a form. For instance, in an email I’m promoting the patron to borrow a book for their book club. Usually they’d click the link and then have to add the book title in the form. However, with a little URL magic, you can do this bit of work for the user.

How to prepopulate fields

* Go to http://prepopulate.jotform.io/, login, and select your form (you have to scroll).
* On the form, enter the info you want prepopulated.
* Click on Generate URL at the top.
* On the next screen, you’ll see a custom Full URL.

If you’re just linking directly to the form

* Just copy the URL generated by step 4.
* Make sure to not send people this link as displayed above. Instead, link the text like so.

If your form is embedded on the website

* Copy only the text starting with the ? the very end.
* You’re copying something like: ?book=Twilight
* Pull up the website page which has the embedded form on it.
* Paste the text you copied to the end of the URL. It’ll look something like this:
https://www.example.com/submit-request?book=Twilight
* Make sure to not send people this link as displayed above. Instead, link the text like so.

Planning Overall Library Program Themes for the Year

One of my jobs as publicity manager is to help departments find opportunities to work together on programs and services. As such, I found out their general program themes for the year. I then grouped these together in our wiki under two organizational schemes: by department and by season.

The information is pretty general so it remains flexible. For instance: Summer Reading Kick-Off: June. It doesn’t list the program theme or the actual start date. I arranged the seasonal information by ABC order. By department, by time.

I then immediately made use of the new resource by linking to it in an email for publicity planning, “Need help thinking ahead as to what we’ll be up to in this time period? View the seasonal program guide.”

New Census Bureau Tools for Businesses

I just saw a very exciting update from the U.S. Census Bureau. The Census Bureau has two new features for businesses:

* Regional Analyst Edition (click on My Location to have it auto-generate the location)
* Census Business Builder: Small Business Edition

These tools will help businesses better target their customers. I’m interested in this thanks to my previous work with my colleague, Mallory, on tools librarians can use to help their business patrons.

Wanted: Help Archiving the EPA Website

I’ve found a way to make myself useful. I’m adding pages from the EPA’s website to the Internet Archive. As such, I’ve found perhaps hundreds of pages which are not archived yet. This bookmarklet is easier to use than the official one. Why? It does it within the page w/o opening a new one.

What to Do
* Install the bookmarklet above to your browser.
* Go to a page of links (like the first one in the following section) and then CTRL + left click on all the links.
* Then go through each page clicking the bookmarklet.
* When you’re done, work your way back across each open tab by clicking the back button. Then scroll down and look for additional links + PDFs.
* Close the tabs as you work your way back across them.
* Sometimes it’ll time out so you need to hit the back button, then try the bookmarklet again.

Things to Know
* If it’s a PDF, it usually downloads to your computer. Annoying. Right now I don’t know how to get those in the Internet Archives, but hold onto them.
* If the PDF is hosted online, you can click the bookmarklet to add it to the Internet Archive.
* If the website/page doesn’t allow robot.txt, you can’t add it to the Archive.
* If you notice that you’re working through pages which have been recently archived, go find another set to go through. It’s a better use of your time to find pages which have never been archived before. These random reports haven’t gotten any IA love before.

Pages to Start With
* Air Research Products in the Science Inventory
* Science Inventory pages

If you’re interested in strategically going through the EPA site with me, let me know. We can make a plan of action to go through and get the pages in.

Current Publicity Workflow

I’m feeling a little better about how I’m doing at this new job. Using Asana to track projects is working well. My current workflow is such:

* Get a request for publicity help. If it’s complex, I schedule an in-person meeting.
* We meet in person. They tell me the scope, their dreams, and desired outcome. We discuss how to reach their audience. We sketch out our plan of attack.
* I go back to my desk and put it in Asana. Simultaneously, I cross-check due dates with my Google Calendar where I track the blocking out of time to work on projects.
* Then I use Asana’s project-level print view to print out the plan and deliver it to my colleague. It seems to impress the hell out of them to get a physical sheet of paper in hand.

I’m also trying to live up to the advice I got from the Cecily Walker interview I co-did with Michael Schofield on behalf of Circulating Ideas. The advice: pad your time. When I’m setting up my schedule for the following weeks, I block out an hour a day called “Downtime.” This is really code for “wiggle room” as things come up as they always do. Yesterday for instance, my day got hijacked for three hours. However, at the end of the day, I managed to get all my major tasks done thanks to the set aside time.

Facebook Services

Courtesy of Darien Library


Do you know if you convert your library’s Facebook account into a business account, you can add Services to it? We made the transfer so we could buy ads on Instagram. Insider tip: this gives you an extra hurdle to do anything even something simple like post on your account.

I couldn’t figure out what I should really do with services. There is pretty much no info on FB itself (kept 404-ing) and the web wasn’t talking about. So I winged it.

Things to know:

* You can’t adjust the images’ location like you can for your profile pic.
* You only get 200 characters to describe it.
* I chose to use generic terms for services (i.e. not giving the actual name of our cafe) since I figured people would be searching for cafe instead of Cafe’s Official Name. I may even go back and change it out to Food and Drinks to be even more generic.
* I’m not sure what the best practice is for how many services to add, so maybe experiment with what looks right to you?
* Organize your services to be in most appealing order to your community.

The little bit of info I gleaned off the web is that Facebook Services is meant to be a way to connect users with local services. I hope it pans out for us! I’d like to talk with other libraries which are using the Services tab.