ADD NEW COLUMN TO GOOGLE SPREADSHEET

Yes, ALL CAPS, here is EXACTLY what I want to do:

Add new column to google spreadsheet using java api

Maybe now that term will be google-able? As with everything handy, it’s impossible to find what you need in the documentation, or on the broader internets. The fun part about google spreadsheets via the API is that its a lot like Amazon SimpleDB, since your “row” schema will be as large as your widest row. Because the only objects you have to work with are “List Feeds” (rows) and “Cell Feeds” (cells) there isn’t much of a concept of “Columns”, except in the ListFeed as the entry.getCustomElements().getTags() collection.

Bottom line, all I wanted was to see if a column existed or not, and if not, add it to the sheet. I haven’t seen any way to do it while in the ListFeed, so its apparent it has to be done from the CellFeed. You’ll have to do some ListFeed-ing, then add it in the CellFeed, then you can go back to your ListFeed and hopefully access your new column.

This post has the solution:
http://stackoverflow.com/questions/4348610/how-do-i-create-the-first-line-in-a-new-google-spreadsheet-using-the-api

As the only useable google result from “Add a column”, its about adding columns to a blank sheet – also very useable and solved my problem. So, that should fix the Google searching for them. On to my problem:

I have:

  • 4 mandatory/default columns in my sheet to start
  • a configurable amount of additional “template” columns I want to populate.

I want:

  • Update data in my additional columns on a regular basis using the data in the 4 initial columns.
  • To take my configurable list of additional columns and compare it to the columns that currently exist:
    • Check each time for my additional columns when I start updating
    • Add the additional columns if they dont exist

I’m not done yet but I’ve got it figured out so I’ll outline my steps in a pseduo-code manner.  This is using the java gdata APIs.  I’ve left out accessing and getting your target spreadsheet, workseet, listfeed, and cellfeed, as well as whatever logic you might have to determine the number of columns you need to add.  We’ll pretend here that each time I want to update I’ll compare my desired additional columns to the currently available columns (using CustomElementCollection.getTags()) and the newcolumns list will contain any columns to add:

SpreadsheetEntry spreadsheet;  //my spreadsheet
WorksheetEntry worksheet; //my worksheet
ListFeed listfeed; //my ListFeed "rows"
CellFeed cellfeed; // your CellFeed from your worksheet
List newcolumns = new ArrayList(); //my pre-determined columns I need to add
int maxcolumns = 0;
   for(ListEntry entry : listfeed.getEntries()){
      //this is your max number of columns
      //this will loop, but its practical to do it for just the first listfeed entry
      maxcol = entry.getCustomElements().getTags().size();
   }

    for(String newcolumn: newcolumns){
        maxcol++;
        CellEntry newcell = new CellEntry(1,maxcol,newcolumn);
        cellfeed.insert(newcell);
        System.out.println("Inserted new column"+maxcol+ ":" + newcolumn);
    }

#boom

Leave a Reply

Your email address will not be published.