@Override
protected SpreadsheetRow insertRow(HashMap<SpreadsheetColumn, String> values) {
try {
// Ensure the worksheet exists in the spreadsheet
if (!this.existsInSpreadsheet) {
throw new RuntimeException(this.nonexistentWorksheetMessage());
}
// Ensure the worksheet knows its column IDs
if (!this.hasGoogleIdsSet) {
this.getGoogleColumnIds();
}
// Create a Google representation of the new row
ListEntry row = new ListEntry();
for (SpreadsheetColumn sc : values.keySet()) {
GSColumn c = (GSColumn) sc;
row.getCustomElements().setValueLocal(c.getGoogleColumnId(), values.get(sc));
}
// Insert the row
row =
((GSModel) this.model)
.spreadsheetService.insert(this.worksheetEntry.getListFeedUrl(), row);
// Update the worksheet entry and list feed entry
this.getSelf();
return new GSRow(this, row);
} catch (Exception e) {
return null;
}
}
@Override
public List<SpreadsheetRow> findRows(SpreadsheetColumn column, String value)
throws EolRuntimeException {
List<SpreadsheetRow> rows = new ArrayList<SpreadsheetRow>();
// Ensure the worksheet exists in the spreadsheet
if (!this.existsInSpreadsheet) {
// throw new EolRuntimeException(this.nonexistentWorksheetMessage());
return rows;
}
// Ensure the worksheet knows its column ids
if (!this.hasGoogleIdsSet) {
this.getGoogleColumnIds();
}
if (!(column instanceof GSColumn)) {
throw new RuntimeException("The given column must be of type GSColumn.");
}
GSColumn c = (GSColumn) column;
if (c.isMany()) {
// Get all rows contained by the worksheet to check every cell manually
for (SpreadsheetRow r : this.getRows()) {
// Get all visible values
List<String> cellValues = r.getAllVisibleCellValues(c);
// Mark row to be returned if the search value is in the cell
if (cellValues.contains(value)) {
rows.add(r);
}
}
} else {
try {
// Use Google's structured query feature to search for the value
URL listFeedUrl =
new URI(
this.worksheetEntry.getListFeedUrl().toString()
+ "?sq="
+ c.getGoogleColumnId()
+ "=="
+ URLEncoder.encode("\"" + value + "\"", "UTF-8"))
.toURL();
ListFeed lf =
((GSModel) this.model).spreadsheetService.getFeed(listFeedUrl, ListFeed.class);
for (ListEntry r : lf.getEntries()) {
rows.add(new GSRow(this, r));
}
} catch (Exception e) {
throw new EolRuntimeException(e.getMessage());
}
}
return rows;
}
/*
* Loads the column ids from Google.
*/
protected void getGoogleColumnIds() throws EolModelLoadingException {
try {
System.out.print("Loading Google ids for '" + this.getName() + "'... ");
// Ensure the worksheet exists in the spreadsheet
if (!this.existsInSpreadsheet) {
throw new RuntimeException(this.nonexistentWorksheetMessage());
}
// Reset the flag early to avoid cycles
this.hasGoogleIdsSet = true;
// Position of the last header column
int maxColIndex = this.getHeader().last().getIndex();
// Determine the first blank row
int rowIndex = this.getRows().size() + 2;
// If the worksheet dimensions are smaller than the first blank row, then expand the worksheet
if (this.worksheetEntry.getRowCount() < rowIndex) {
this.worksheetEntry.setRowCount(rowIndex);
this.worksheetEntry = this.worksheetEntry.update();
}
// Fill the first spare row with temporary values
URL cellFeedUrl =
new URI(
this.worksheetEntry.getCellFeedUrl().toString()
+ "?min-row="
+ Integer.toString(rowIndex)
+ "&max-row="
+ Integer.toString(rowIndex)
+ "&min-col=1"
+ "&max-col="
+ Integer.toString(maxColIndex)
+ "&return-empty=true")
.toURL();
CellFeed cellFeed =
((GSModel) this.model).spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
for (CellEntry cell : cellFeed.getEntries()) {
cell.changeInputValueLocal("TEMP");
cell.update();
}
GSRow row = null;
// Get the row from the worksheet to populate the Google Unique Id set
URL listFeedUrl =
new URL(
this.worksheetEntry.getListFeedUrl().toString()
+ "?start-index="
+ Integer.toString(rowIndex - 1)
+ "&max-results=1");
ListFeed lf = ((GSModel) model).spreadsheetService.getFeed(listFeedUrl, ListFeed.class);
List<ListEntry> list = lf.getEntries();
if (list.size() == 1) {
ListEntry le = list.get(0);
int colIndex = 1;
// Iterate over all returned tags - Google preserves column ordering in the set
Iterator<String> it = le.getCustomElements().getTags().iterator();
while (it.hasNext()) {
GSColumn c = (GSColumn) this.getColumn(colIndex);
// If the column is known, then associate the id with it
if (c != null) {
c.setGoogleColumnId(it.next());
} else {
it.next();
}
// Stop once the final known column is reached
if (colIndex == maxColIndex) break;
colIndex++;
}
row = new GSRow(this, le);
} else {
throw new Exception(
"List feed failed to return the row of temporary values for learning the Unique Column Ids.");
}
// Delete the temporary row
this.removeRow(row);
} catch (Exception e) {
throw new EolModelLoadingException(e, this.model);
}
System.out.println("<< done");
System.out.println(this);
}
