SHAREPOINT 2007: INTEGRATING THE GOOGLE CHART API IN SHAREPOINT LIST

INTRODUCTION

In this article, I’ll show you how to use Google Chart API in SharePoint 2007 (MOSS):

     1. Create a Custom List in SharePoint Server 2007
     2. Create a Web Part page (used to display graphical representation of the list data)
     3. Integrate Google Chart API into SharePoint

CREATE A CUSTOM LIST IN MOSS 2007

Follow the steps below in order to create a new custom list.

1. Click Site Actions > Create.

2. Under Custom Lists, select Custom List.

3. Fill in Name and Description.

For this demonstration, name the list Projects.

4. Click Create.
    The Projects custom list is created.

5. Click Settings > Create Column.

    Enter the name of the new column, select the data type, and details for each column you create.

    For this demonstration, I created three new columns in Projects:

       • Start Date(Date)
       • End Date (Date)
       • Status (Choice list: Complete, Deferred, In Progress, Not Started)

6. Fill this list with sample data to create the Google Chart.

    To create sample data, click New.

7. Fill the New Item form, and click OK.

8. Repeat until you have enough data to demonstrate your Google Chart API.

    For this example, I created 10 items.

CREATE A WEB PART PAGE

Use the web part page used to display graphical representation of the list data:

1. Click Site Actions > Create.

2. Under the Web Pages section, select Web Part Page.

3. Fill the required information for the New Web Part Page:

    • Name: SamplePage
    • Accept defaults

4. Click Create.

The new Sample Page is created as below.

INTEGRATE GOOGLE CHART API INTO SHAREPOINT

To display the graphical representation of any list data, we first need to decide which column of the list we want to use as a graph criteria. Then we need to create a “grouped by” list view on that column. For chart to be displayed, the “grouped by” list view must be on the same page.

I use Status as the criteria for the graph. We first need to add the Projects list web part on the Sample Page to display Projects list “group by” Status column.

1. Click Add a Web Part in any of the zone of the page.

2. Under the Lists and Libraries section, select the Projects list (or the name of your list).

3. Click Add.

    We need to edit this list to create a new view grouped by the Status column.

4. Click Edit > Modify Shared Web Part.

5. Click Edit the current view link.

6. Scroll to the bottom of the page, and click to expand Group By section.

7. For First group by the column: select the Status column.

8. Select your preference for Ascending or Descending order.
9. Click OK.

    The newly created list view will be added on the Sample Page.

Now to add graph for the list view, we need to add another web part on the Sample Page: Content Editor Web Part

10. Click Site Actions > Edit Page.

11. Click Add a Web Part above the Project list view just added.

12. Under the Miscellaneous section, select Content Editor Web Part.

13. click Add.

14. Select Edit > Modify Shared Web Part.

15. Open the Source Editor (right side).

16. Copy the following code and paste into the Text Entry dialog box:

<div id="jLoadMe" class="content"><strong>Pie Chart Example Using Google Charting API</strong></div>

<script type="text/javascript">if(typeof jQuery=="undefined"){

 var jQPath="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/";

 document.write("<script src='",jQPath,"jquery.js' type='text/javascript'><\/script>");}

</script>

<script type="text/javascript">

$("document").ready(function(){

 var arrayList=$("td.ms-gb:contains(':')");

 var coord= new Array();

 var labels= new Array();

 $.each(arrayList, function(i,e) {

  var MyIf= $(e).text();

  var txt= MyIf.substring(MyIf.indexOf('(')+1,MyIf.length-1); // Extract the 'Y' coordinates

  coord[i]=txt;

  var txt1= MyIf.substring(MyIf.indexOf(':')+2,MyIf.indexOf("(")-1); // Extract the labels

  labels[i]=txt1+"("+txt+")";   //add also coordinates for better read

 });

 var txt= coord.join(",");

 var txt1= labels.join("|");

 // Adjust Chart Properties below - See Google Charts API for reference

 var vinc= "<IMG src='http://chart.apis.google.com/chart?cht=p3&chs=750x200&chd=t:"+txt+"&chl="+txt1+"'/>";

 $("#jLoadMe").append("<p>"+vinc+"</p>")

});</script>

17. Click Save.
      You will see that the graph has been added into the web part.

18. Click OK. If you see the Security Warning box as below, click No.

19. Click Exit Edit Mode.

Add JavaScript Date Validation into List Item forms (in NewForm.aspx)

I want to validate two fields on a new list item form by invoking JavaScript custom function. They are two date fields and I want to ensure that the end date can't happen before the start date. My first idea was to attach a validation function on the onclick event of the Submit button.

I started by inspecting the generated HTML of the form. The Submit button already has a onclick() code which is:

if (!PreSaveItem()) return false;WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$m$g_b8be167a_5691_41d0_975c_b269cd345fc6$ctl00$toolBarTbltop$RightRptControls$ctl01$ctl00$diidIOSaveItem&quot;, &quot;&quot;, true, &quot;&quot;, &quot;&quot;, false, true

Searching in the SharePoint JavaScript files in the LAYOUT folder, I found the definition of PreSaveItem function in FORMS.JS file. It simply invokes PreSaveAction function, if defined.

Finally, it was just a matter of inserting a custom function named PreSaveAction in a <SCRIPT> block of the NewForm.aspx (and EditForm.aspx). I also used the date parse code from this forum.

The code I put in NewItem.aspx is like this,

Note: Write your function just below the PlaceHolderMain line:

<asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">

For validating two dates from two calendar controls:

function PreSaveAction()

{

    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Contract Date"); 

    var date2 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Contract End Date");

    var arrDate1 = date1.value.split("/");

    var useDate1 = new Date(arrDate1[2], arrDate1[1]-1, arrDate1[0]);

    var arrDate2 = date2.value.split("/");

    var useDate2 = new Date(arrDate2[2], arrDate2[1]-1, arrDate2[0]);

    if(useDate1 > useDate2)

    {

        alert("The end date cannot happen earlier than the start date");

        return false; // Cancel the item save process

    }

    return true;  // OK to proceed with the save item

}

If you want to validate the date with current calendar date and check the validation on button click, then the function is as below:

function PreSaveAction()

{

    var DateBox = document.getElementById('ctl00_m_g_b8be167a_5691_41d0_975c_b269cd345fc6_ctl00_ctl04_ctl00_ctl00_ctl00_ctl04_ctl00_ctl00_DateTimeField_DateTimeFieldDate');

    if (DateBox != null)  {

             var value1 = DateBox.value;

               if(value1 != null && value1 != '' ){

                var currentTime = new Date();

         var month = currentTime.getMonth() + 1;

       var day = currentTime.getDate();

       var year = currentTime.getFullYear();

      var currentdate = month + "/" + day + "/" + year;

      if(value1 <= currentdate){

      return true;

      }

      else

      {

      alert("Selected Date should be less than or equal to today");

      return false;

      }

      }

      else

      {

      alert("Selected Date should be less than or equal to today");

      return false;

      }

      }

}

You can also use this code:

<script language="javascript" type="text/javascript">

function PreSaveAction()

{

var DateBox = document.getElementById('ctl00_m_g_b8be167a_5691_41d0_975c_b269cd345fc6_ctl00_ctl04_ctl00_ctl00_ctl00_ctl04_ctl00_ctl00_DateTimeField_DateTimeFieldDate');

if (DateBox != null) {

var value1 = DateBox.value;

var arrDate1=value1.split("/");

var newmonth= arrDate1[0];

var ctrlDay = arrDate1[1];

var ctrlYear = arrDate1[2];

if(value1 != null && value1 != '' ){

var currentTime = new Date();

var month = currentTime.getMonth() + 1;

var day = currentTime.getDate();

var year = currentTime.getFullYear();

var currentdate = month + "/" + day + "/" + year;

if((newmonth <= month) && (ctrlDay <= day) && (ctrlYear <= year)){

return true;

}

else

{

alert("Please select TimesheetDate upto Today!");

return false;

}

}

else

{alert("Please select TimesheetDate upto Today!");

return false;

}

}

}

</script>

VSeWSS 1.3 Post-Install Configuration

If you’re trying to install VSeWSS 1.3, the latest version of the Visual Studio Extensions for Windows SharePoint Services, you may find that you need to tweak a couple of things post-install to make sure the VSeWSS 1.3 web service is configured correctly.
To give you a little context, when I first installed the default installation and then created a web part project to test deployment I was getting the following error. This was resulting from the VSeWSS 1.3 WCF web service not having the appropriate permissions.


Note: To install VSeWSS 1.3, Kirk Evans gives a good overview here:
http://blogs.msdn.com/kaevans/archive/2009/03/17/installing-vsewss-1-3.aspx
For post-install config—i.e. to rid yourself of this error message, you might find you need to follow these steps:
1. Open IIS and verify that you have a VSeWSS web service app.

 2. Click Advanced Settings on the  VSeWSS web app to ensure it is using the SharePoint Central Administration v. 3 application pool.

3. Go to the SharePoint Central Administration v.3 application pool and make sure that the Identity is running as NetworkService.

4. Go to Computer Management and click Groups, Administrators and then Add. You’ll want to, if it’s not already, add the NetworkService to the Administrators group. (Click Advanced, Find Now, and then select Network Service from the results.) Click OK to get out of the Administrators Properties dialog.

 
5. Reset IIS.
Once this was complete, I was able to go back and create a web part and successfully deploy into my local SharePoint site.

Deploying a WebPart Solution in SharePoint 2007 the simple way

Deploying web parts into MOSS 2007 isn’t exactly straight forward and after looking around the web for a while it became apparent that there is no ‘standard’ way for deploying them. I looked at a few different options and found that creating a Solution file using Visual Studio’s CAB Setup Project was the easiest and most reusuable way of accomplishing this task.
Below is a step by step guide to developing and deploying a simple WebPart.
First we need to create a WebPart to deploy, the easiest way I have found of doing this is to download the Visual Studio SharePoint extensions from Microsoft which can be located here.
After installing the extensions open Visual Studio and create a new project. You should now have some extra project templates under the SharePoint section.



Select ‘Web Part’ project, give it a name and click OK.
Visual studio will now create a basic Web Part class for you to edit.
My Web Part was called ClientViewerWebPart and I inserted some code into the overridden Render method which basically outputs data from a SharePoint list. This code looks like this so far:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

namespace ClientViewerWebPart

{

    [Guid("6bf05a67-118d-4cc4-80f7-b923be02773a")]

    public class ClientViewerWebPart : System.Web.UI.WebControls.WebParts.WebPart

    {

        public ClientViewerWebPart()

        {

            this.ExportMode = WebPartExportMode.All;

        }

        protected override void Render(HtmlTextWriter writer)

        {

            SPSite site = new SPSite(“http://leesbs/”);

            SPWeb web = site.OpenWeb();

            SPListCollection collection = web.Lists;

            SPList list = collection.GetList(new Guid(“{936518FF-CC2E-4BD9-ABB9-0580EA04BCD6}”),                         false);

            for (int index = 0; index < list.Items.Count; index++)

            {

                SPListItem item = list.Items[index];

                writer.Write(“<table>”);

                writer.Write(“<tr><td>Client Name</td>”);

                writer.Write(“<td>” + item["Client Name"].ToString() + “</td></tr>”);

                writer.Write(“</table>”);

            }

        }

    }

}

Next we need to tell SharePoint to allow this web part to be executed from a partially trusted location. We do this by making an entry into the web parts AssemblyInfo file.
In Solution Explorer Expand Properties and open up the AssemblyInfo.cs file. At the bottom of the file insert the following:
[assembly: System.Security.AllowPartiallyTrustedCallers()]
Now we need to add a manifest file to out Web Part project. This manifest file is what defines our solution and tells SharePoint everything it needs to know about our Web Part.
Add a new XML file to tthe project and rename it manifest.xml. Insert the following XML into the file:

<?xml version=“1.0“ encoding=“utf-8“ ?>

    <Solution xmlns=“http://schemas.microsoft.com/sharepoint/“

                    SolutionId=“{E3CF88A3-0EC3-49b9-B09E-5F84417EC6ED}“>

        <Assemblies>

            <Assembly DeploymentTarget=“WebApplication“

                                  Location=“ClientViewerWebPart.dll“>

                <SafeControls>

                    <SafeControl Assembly=“ClientViewerWebPart, Version=1.0.0.0, Culture=neutral,                                                                                         PublicKeyToken=3858ebd08dca7ee0“

                                            Namespace=“ClientViewerWebPart“ TypeName=“*“/>

                </SafeControls>

        </Assembly>

    </Assemblies>

</Solution>

You need to provide your own GUID value for the SolutionId attribute, you can do this within Visual Studio by using the GUID Generator under the Tools menu. You also need to enter your assemblies PublicKeyToken value which can be found by either using ILDASM.exe or Reflector.

The last thing we need to do to our Web Part project is make sure it has a strong name when it’s compiled, you can do this either using the SN.exe command line tool, or opening the project properties from within Visual Studio and navigating to the Signing section. From here you can create a new key file which will be used to strong name the assembly at compile time.

Now we have a fully working Web Part which can be compiled, what we need to do now is deploy this Web Part into our SharePoint site.

We are going to use a Visual Studio setup project to accomplish this, so add a new project to the same solution your Web part project is in, and create a CAB setup project. I called my setup project ClientViewerWebPartSetup.

Right click on the project and goto Add -> Project Output, from the ‘Add Project Output Group’ dialog box, select your Web Part project and then select ‘Primary Output’.

Repeat the step above but this time instead of selecting ‘Primary Output’ select ‘Content Files’.

After this is done you should have a solution that looks something like this:

Now you can compile your setup project, this will create a cab file containing the WebPart assembly and the manifest.xml file so all we need to do now to create our SharePoint solution file is rename our .cab file to .wsp.

We are now ready to deploy this solution file into SharePoint and we do this by using the STSADM.exe tool. This tool is located in your SharePoint installation directory under the bin folder mine was located here C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN. I strongly suggest you add this path to you PATH environment variable for ease of use.

You need to use the addsolution argument to the STSADM tool the first time you deploy a Web Part into your SharePoint site. When updating the Web Part you can use the upgradesolution argument.

The following is how I deployed my Web Part into my SharePoint site for the first time.

Great! Your Web Part is now deployed into your SharePoint site, or is it?

We need to do one more thing to make out Web Part available to our SharePoint site.

Open up Central Administration and navigate to ‘Operations’ under ‘Global Configuration’ click ‘Solution Management’. You should see your Web Part Solution sitting in the list with ‘Not Deployed’ as it’s status. Click on the Solution and click the ‘Deploy Solution’ button.

You should now be able to add your Web Part to any sites Web Part gallery.

Hope this guide has been useful, it’s quite a lengthly process do first time, but should get easier the more times you do it.