pgwalsh

Search this site

What I do...

Below are some links to my start-up. I really appreciate anyone that becomes a fan. It's just two of us trying to get the business off the ground. We're small and local and could use the support.

All Natural juice drinks made with Stevia.

Like us on FaceBook, please. We have a special promo that will start in the middle of February, 2011. If you like us you'll be automatically included. However, it's limited to US residents only.

You may follow us on Twitter too.

If you really need to contact me, you can use any of the forms. You may also email me at this sites name at gmail.com. I'll do my best to get back to you in a timely manner.

-Peter

Comments How To

This is alternative format version of Steven Hind's excellent visitor comments form, which can be found here. Follow his directions or video tutorial and then if you want your comments section to look like mine, use the content format below for your List page. 

Please read the Spreadsheet Bug below because of a bug found by @thelam83. If you find this all too confusing, let me know and I'll rewrite it. Certain things have developed overtime that have changed many forumla's and the way to do things. 

Total comments added up for the list page. Put this in your A1 row of your list page. 

=("Total Comments: " & counta(Sheet1!A2:A))

This next part is the Table Cell Content Format for the list page. Make sure to put this in your A2 row of your list page and nothing in B onward.  
Explanation:

  • ArrayFormula( "all your cells references" )  Creates an array of cells matching your ranges. Essentially repeats downward.
  • Sort("your reference, Sheet1!A2:A, false) Sorts your table by first column.
  • IF(ISBLANK("name reference"), "blank", "By:") This checks if the cell is blank or not. If you don't have this statement you will have By: in every blank cell. Not good.
  • CHAR(10) A newline. This allows your to have some separation from user name and comments.

Here's the entire formula to cut and paste if you wish. Remove spaces if you get an error.

=ArrayFormula(sort(Sheet1!A2:A&IF(ISBLANK(Sheet1!B2:B), " ", " By: ")
&Sheet1!B2:B&CHAR(10)&CHAR(10)&Sheet1!C2:C&CHAR(10)
&CHAR(10),Sheet1!A2:A,false))

Q&A

Q. How long does it take to post?
A. Varies, but it does require a refresh and my best estimate is 3 to 5 minutes.

Q. How can I do an approval process before the comments post.
A. See below for an approval method.

Approval Process with List Page

Commenter Steve wanted to see the formula for an approval process before posts are shown. It will only show posts based on a value of Y (you can change this value to whatever you want). I have color coded what you will need to add. I hope this helps.

Step 1: On your Sheet1 add a column called Approval. Each corresponding cell you want viewable add a "Y." When commenters add a new comment the Approval cell will be blank and the comment will not be shown. Add a "Y" to approve the comment or a "N" so you know the comment was not approved. If you leave it blank you may forget whether or not you approved the comment.

Step 2: Remove the original formula on your display sheet and add the one below or just add the two FILTER functions to your formula and you're good to go.
  • FILTER, Sheet1!D2:D="Y" : Only show rows in column D with Y for approval.
The filter is added twice. The first filter encapsulates the core information to be displayed. The second filter is for the SORT function, which sorts by the date column. You must filter the sort date column or you will get a mismatch error because the first filter removes any rows without a Y.

=ArrayFormula(SORT(FILTER(Sheet1!A2:A&IF(ISBLANK(Sheet1!A2:A), " ", " By: ")&Sheet1!B2:B&CHAR(10)&CHAR(10)&Sheet1!C2:C&CHAR(10); Sheet1!D2:D="Y"),(FILTER(Sheet1!A2:A ; Sheet1!D2:D="Y")), FALSE))

This is assuming you have D set as a blank column. You can use any blank column. To approve the post, just type Y in the column or put an N if it's unapproved.


Note: Do not used the image formula for approval as it doesn't have approval.

Spreadsheet Bug

@thelam83 found a bug that's rather important, so if you want to protect your workbook so only specific sheet is viewable then read the following. 

Bug: Anyone logged in to google will be able to see all your spreadsheets in a workbook if you make any Sheet publicly viewable. 

Solution: To avoid having having your entire workbook viewable to anyone logged into Google do the following. Make your workbook not viewable to anyone but yourself. Create a new workbook with a single sheet. You will then reference your original workbook in this new workbook. 

Reference Formula: Place this in cell A2. 

=importRange("KEY","List!A2:A"&VALUE(importRange("KEY","List!B1")))

Your KEY is the last part of your URL for the workbook you are referencing. It follows ?key=

http://spreadsheets.google.com/ccc?key=YOUR-KEY

In the above formula List!B1 is a cell I added that has the count of cells A2:A of the referenced workbook. You need a cell in the sheet your referencing ( it could be another sheet in the referenced workbook) that counts the cell range you are referencing. 

Unfortunately importRange("KEY", "ListA2:A") will not reference from A2 to the end of A. It's not currently supported, so you need to create a cell within the original workbook that counts those table cells. I just did it to the right of the data that I'm referencing. 

You will then make this sheet in your new workbook viewable. WIth just a single sheet, everyone will only get a single sheet. Clean & Simple. 



comment_how_to_display



Comments_How_To