Google Sheets Powered PO Generator

Writing purchase orders is one of the least favorite parts of my job. It’s often a repetitive and monotonous task that requires a deep dive into a file cabinet, a massive catalog, or both. I created this spreadsheet tool to make POs a little less onerous. This system keeps track of your orders, allows you to take notes, monitors your budget in different budget codes, and will actually generate the printed PO sheet! Make a copy if you would like to use the sheet. I hope you find this as useful as I do.

Setting up the sheet is easy. Just add your vendor information to the Vendor Tab. As a general rule, anything in the sheet with a yellow background can be edited. Anything with a grey or black should not be edited. These are controlled with functions. Adding vendor addresses is a one-time outlay of work. The address will auto-fill on the POs. I include my own information here, too, because there are certainly times when I purchase something from a vendor not on the bid list (Amazon, anyone?) and need to submit a PO with me as the vendor. You can add vendors as you need them, secure in the knowledge that the address will be ready the next time a PO is generated for that seller.

Also on the Vendor sheet is a budget code table. I have two budget codes as a technology teacher, one for supplies and another for equipment. In order to keep track of your codes, add the total amount you have to spend and the codes themselves in the appropriate columns (yellow only.) You’ll notice that the sheet automatically keeps track of the amount left in each budget code and the supplies code has a function that helps me keep track of per student expenditure.

The “Master Sheet” is where I fill out my orders (yellow cells only.) It's fairly self explanatory. One this thing I need to point out: the first two columns seem redundant but are not! The first column allows me to specify an order on a specific day, the second column is only the vendor name, linked to your vendor sheet. This second column controls the address information and is independent of the date. Both are necessary!

Once your order is recorded on the “Master Sheet” you can generate your PO. Now you have one place for all your PO records and you don’t have to worry about handwriting a PO form. Better yet, next year I have a clear record and can reorder easily. Open the PO Generator tab. You will notice four yellow cells, three with drop down boxes. Select your Vendor, which will populate all of the address fields. Then, select the date the PO was written, which is fed from the first column of the “Master Sheet.” Finally, select the budget code, write in the potential shipping cost, and add your name in the “Requested by:” field.

Your PO Is ready to print! This has made PO writing so much easier for me. I keep notes on my Master Sheet, such as when an order arrives, or if I was unhappy with the product. My record keeping is better and the process is a lot faster. This was designed using the Scarsdale PO form, so you may need to redesign the PO Generator layout. Let me know if you use this and if you’ve made it better!

Creating a Google Form Powered File Sharing Site in Two Ways: Nose to Tail

 

This blog post has been sticking in my head over the last day and I decided to rewrite sections. The whole system that was used to create the Scarsdale Teacher Share is complex, and while it works for us, it isn’t for everyone. There is a lot to be said for simplicity and I’d like to include a less complex alternative that accomplishes a lot of the same stuff.  In the revised post below, I’ll explain the easier method first and then continue to the more complex version. The whole thing is pretty long - but I hope it's useful.

A couple of weeks ago I had the honor of presenting at the NY/NJ Google Summit.  Lisa Thuman does an amazing job putting the event together and I’m always impressed at the caliber of presenters and participants.  This year I presented on the Google script powered teacher sharing site that I’ve blogged about before. I promised the good folks in my session (thank you all for coming!) that I would write something to outline the system; this is that post. I meant to get it out sooner, but in the immortal words of John Lennon, “Life is what happens to you while you're busy making other plans.”

        This process allows a district to create a Google Form powered Google Site. A teacher can submit a file to share, organize it with self selected or generated tags. The simpler method uses a special gadget to interface with the form submissions and the shared resources.  The more complex example creates Google Site pages created automatically using the information given by the form.  For the complex example, I created a set of simple demo files that you can use here. The files that run the Scarsdale site are more complicated, but all of the basic functionality can be found in these demo files.

      With either method you must create a spreadsheet using the old sheets and build a form. Decide whether the system will work with a domain or be public.  For our district sharing site, the form is limited to the domain. Your spreadsheet needs to collect at least six things:

1. Name of the contributor (Text question or automatic in a domain-only form)
2. Email of the contributor (Text question or automatic in a domain-only form)
3. Name of the resource (Text question)
4. A description of the resource being shared (Paragraph text question)
5. A field for tags (Text question with an “other” option)
6. The URL of the drive resource that is being shared (Paragraph text question)

Once your form is in place, return to your spreadsheet and install the New Visions Form Folio Script. You will have to determine whether you are collecting usernames at this stage. You will also need to determine whether shared resources are copied and how they will be organized by formFolio.  The demo example uses the “Keyword” column to organize resources. A folder is then created for each keyword.  Scarsdale collects usernames in our domain-only share.  These usernames become our method for organization. We also copy the shared resources so that resources are stable despite faculty changes.

After you install formFolio, add the following sheets to the document:

• formRanger List
• csv horz array
• HTML
• buildPages (not needed in the simple method)
• createPages (not needed in the simple method)
• Awesome Tables (optional in the complex method, but you’d be crazy not to)

The “formRanger List” and “csv horz array” use the dynamic tagging system I described in a previous post.

The Simple Method: formFolio & Awesome Tables

The basic components for a teacher share are in place once you have your form and have installed formFolio.  Next, let’s create the interface for the shared resources using the Awesome Tables gadget.  Create a query on A3 of the Awesome Tables sheet. Use the query to grab the form submission data that you want to use in your interface. In the example below, I query the “createPages” sheet because the example showcases the complex method.

=QUERY (createPages!B2:J, "SELECT D, E, F, G, H, J", 0)

Then, use the first row to title the columns. These titles will be used in the interface. The second row corresponds to how the columns can be filtered by the end user.  Use the “Awesome Table” documentation to set this up. The spreadsheet has to be public in order for Awesome Tables to work.  If you don’t want to make your whole spreadsheet public, use two additional Google sheets, along with the New Visions script pushData to move the Awesome Tables page to a public sheet.

The final step in the simple version is to add the awesome table gadget to a Google Site page.  This is also explained in the “Awesome Table” documentation.  You can get creative with how the filters behave in the awesome tables sheet and a lot an depend on the data you’ve collected in the submission form.

The Complex  Method: formFolio & Google Site Page Creation

Let’s return to the complex method if you’re up for it. We need to look first at the “csv horz array” page. In the sharing site example, you’ll notice two differences from the dynamic tagging system I described in a previous post. Column A on the “csv horz array” page is named “Key.” This column combines the submission date and username to create a unique value to use on other sheets for vertical lookup formulas. Column B is named “HTML” and uses an if/then array formula to check whether tags have been assigned to a new resource.  If a tag cell is blank (column F and beyond) the formula uses canned HTML from the “HTML” page. If the tag cell contains a keyword, it is made into an HTML link that searches the site for that keyword, also using the “HTML” page as a reference form the code.

= ARRAYFORMULA ( IF ( F2:F2 = "" , HTML!A10 ,HTML!A7 & F2:F & HTML!A8 & F2:F  & HTML!A10 & (IF ( G2:G = "" , "" , HTML!B7 & G2:G & HTML!A8 & G2:G  & HTML!A10 ) & ( IF ( H2:H = "" , "" , HTML!B7 & H2:H & HTML!A8 & H2:H  & HTML!A10) & ( IF ( I2:I = "" , "" , HTML!B7 & I2:I & HTML!A8 & I2:I  & HTML!A10) & ( IF ( J2:J = "" , "" , HTML!B7 & J2:J & HTML!A8 & J2:J  & HTML!A10 ) ) ) ) ) ) )

This if/then strategy, combined with the HTML page, can be used creatively to build variety into your google site page based on user submissions.  Remember to install formRanger in order to connect the script as described earlier to make your form dynamic.

Next we turn to the “buildPages” sheet. The first column is another "Key" column. Just as on the “csv horz array” page, the “Key” column combines the timestamp and username field.

= ARRAYFORMULA ( B2:B &C2:C )

In cell B2 we use a simple arrayformula to copy over the form responses and formFolio columns without including the headers.  

=ARRAYFORMULA ( 'Form Responses 1'!A2:K )

The column headers are manually named in the first row to keep them friendly for the createPage script that we will discuss in a bit - form response headers have a habit of being a bit long. In column M we will add use a "LEN" function to count the Drive ID column that is created by the formFOlio script.  This returns a number when a resource is added to our share folder.

=ARRAYFORMULA ( LEN ( L2:L ) )

Column N uses an arrayFormula with a VLOOKUP that references the “Key” column. For a simpler method, you can install the copyDown script and forgo the arrayformula altogether.

=ArrayFormula ( IFERROR ( VLOOKUP( A2:A ;'csv horz array'!A$2:B  ; {2} * SIGN ( ROW ( B2:B ) ) ; FALSE ) ) )

Now lets move to the createPages sheet. The first two columns on the "createPages" sheet need to be "ID" and "Product Page." These will be used by the createPages script later on. Cell C1 will use a Query formula to pull the data from the buildPages sheet. Using this query we can reorder the columns and specify a query condition. We will use the LEN column that we added to the buildPages sheet to validate the query.

=QUERY (buildPages!A1:N, "SELECT A, B, C, H, E, J, N where M > 1", 1)

Now you can create the HTML for your Google Site page template. Take a look at the HMTL sheet. You will notice that I divided the HTML with yellow cells.  These cells correspond to the variables that are fed from the Google form.

Finally, we need to add the createPages script.  This essentially uses a script from the book “Google Script: Enterprise Application Essentials” The book is dated now, but you can still pull good ideas from the text.  If you investigate the Script Editor on the demo sheet, you will see that the script builds HTML pages on the Google Site using my code. Whenever a variable is called, the script references the columns of the createPages sheet.

This completes the more complex custom Google Site file repository, powered by a Google form.  To round out your site, you could create an interface with Romain Vialard’s aptly named “Awesome Tables” gadget, or simply set your navigation to auto generate.  A big thanks to New Visions for Public Schools for their amazing script library.

Share Site Revisions

I’ve been debugging the new teacher share site for the past month and it’s been going well.  The most valuable information I've received about the site came from several focus groups with trusted testers. Absolutely nothing beats watching people use a system when you want to learn how to make it better. This is what I learned:

• The directions needed to be better.  They needed to be clear, have multiple levels of depth, and yes, be printable. I added multi-level directions to the “share something” page - I still think they could be better, but it’s a start.
• Each grade level needed a “Special Education” and “Technology" subcategory. I had originally thought these items would fold into regular curriculum subcategories. After all, it’s not the technology, it’s what you do with the technology, right? While this may be true, people wanted to use these categories when they searched for, and shared, resources. This was a tedious fix because all of the conditional questions on the form also needed to be updated.
• There needed to be a K-5 category. It was WAY too onerous to add a file to each grade level manually. This was an involved fix because the HTML template needed to accommodate the new category.  It was an important addition, though, as evidenced by the dull expression left by folks who tried to add resources k-5, one grade at a time.
• There needed be a "Teacher Planning" sub category. Several times someone had something to share that didn’t fit into a curriculum bucket.  Meeting minutes is an example of this - it will be a nice place to keep curriculum committee notes in one place.

Bottom line, focus groups and watching people use your system is such an important way to learn strengths and weaknesses. It’s a little bit of humble pie when you watch someone struggle to use what you've built, but I can’t think of better way to make something better.

Google Site Sharing Repository, Part 2: Creating HTML to link formFolio to Google Sites

It’s taken me a few weeks, but I’ve finally finished the Google Site Sharing repository.  Well, finished might be going too far - I’m in “trusted tester” mode.  This is basically how it works: using a Google form outfitted with +Andrew Stillman's formFolio, a teacher in our district’s domain can submit a resource and organize it with keywords. The Google Spreadsheet uses the information from the form to create a Sites page via the createWebPage method. Next, the pushData script exports the catalog information to a public sheet that informs +Romain Vialard's aptly named “Awesome Table” gadget.  This becomes the front end of the repository.

The Google form is one of the largest I have made with 160 items, most of which are conditional. Teachers are able to select grade levels and subject areas to organize what they are sharing. The formRanger method I described earlier is working really well. Even though I started with a baseline list of keywords, it was immediately evident that keywords could not be static.  I've added new keywords with each test submission. After the system is used for a while, I’ll be able to prune keywords from my original baseline that aren't being used. You can see a copy of the form here without the sign in restrictions so that you can see how it flows.

The spreadsheet builds the HTML for the Google Site using the variables from the form submission. The HTML is then used by the "createWebPages" script method to create the Google Site page. To do this, create two spreadsheet pages in addition to the the Form Submission page. Name one page "HTML" and the other "Page Builder."

Next, construct your new page template in Google Sites, manually using the variables you intend to feed from the form. Use a simple page format - not one with sections or columns. When the page looks right, click the HTML button in the toolbar and copy the code. Return to the Google spreadsheet and paste the code into the "HTML" page.  Scan the HTML for the variables that will be populated by the form submissions and cut the code around each variable, pasting it into sequential cells like this.  Leave out the variables, these will be populated from the form using a spreadsheet function.

Now combine the form submissions with the HTML on the "Page Builder" page using an ARRAYFORMULA. You can get creative with how to do this, such as using IF/THAN and VLOOKUP functions to customize the HTML based on the form submissions. Make sure you use the "Resource URL" column created by the formFolio script to connect the submission to the Google Site. In the demo example, you'll find a simple concatenate function:  

=ARRAYFORMULA ( HTML!$B$4 & 'Form Responses'!A2:A & HTML!$B$5 & 'Form Responses'!D2:D & HTML!$B$6 & 'Form Responses'!J2:J & HTML!$B$7 & 'Form Responses'!C2:C & HTML!$B$8 )

Once you have your HTML code you can invoke the createWebPages method. A decent guide for using this method can be found in +James Ferreira's book, Google Script: Enterprise Application Essentials.  Just a warning: this book was out of date just months after it was released - Google Apps Script evolves too often for print.  That said, the ideas found in the book are still relevant and useful.

What Didn't Work

I wanted to include a Google folder gadget on each page so that viewers could see what was shared. I was able to create code that worked using the method above, as long as it was pasted into a page. Unfortunately, createWebPages seems to have stricter rules for code. No matter what I tried, the script would strip the Google gadget out of the HTML. In the long run, I don't think it's necessary. I actually prefer the plus one gadget.

What Might be Next

I'd like to investigate +Martin Hawksey's post "Using Google Spreadsheets as a Google Analytics Data Bridge".  I think it'd be cool to use analytics to show users what's popular on the site. For the time being, though, the priority is showing teachers the site and providing professional development on how to use it.