Need to embed a string in google common content in a specific place

Question

I am using python to update google spreadsheet. The google python spreadsheet library allows you to insert a row into a table using the InsertRow attribute.

Here is an example:

gd_client.InsertRow(myDict, spreadSheetKey, workSheetKey) 

myDictionary is the dictionary you insert, spreadsheetKey is the key for your spreadsheet and worksheet. The key is the key for your worksheet. However, I would like to insert in a specific row in the middle of my table. This API is only inserted at the end.

Does anyone know if there is a way to do this?

Thanks.

Answer 1

A simple way is to have two sheets in the same spreadsheet. One of them is raw data, because it is inserted from the outside, and the second is calculated (sorting, functions ...), which is based on this raw data.

For example, if you want the data to be sorted by the second column, you can enter a second sheet:

 =EXPAND(SORT(Sheet1!A:D,2,1)) 

This separation is also good, as if you want to update data from outside. It will conflict with operations that you might have on your data, like adding calculated columns to data rows.

Answer 2

How to * insert a row * in the middle of a google spreadsheet via API / OAuth ..... the hard way. (I assume that there is no easy way, because there is an additional request to add this to the API).

  Pattern cellRefPattern = Pattern.compile("R(\\[?)([-0-9]+)\\]?C(\\[?)([-0-9]*)\\]?"); worksheet.setRowCount(worksheet.getRowCount()+1); worksheet.update(); CellFeed batchRequest = new CellFeed(); for (AppCell cellAddr : cellAddresses.values()) { // create a copy of the cell to replace CellEntry batchEntry = new CellEntry(cellAddr.row, cellAddr.col, cellAddr.reference); String updateReference = cellAddr.inputValue; if(updateReference.startsWith("=")) { String removeReferenceBug = updateReference.replace( (CharSequence) "C:R", (CharSequence) "C[0]:R"); Matcher referenceMatcher = cellRefPattern.matcher(removeReferenceBug); StringBuffer restultBuffer = new StringBuffer(); while (referenceMatcher.find()) { try { if(referenceMatcher.group(1).equals("[")) { int rowOffset = Integer.parseInt(referenceMatcher.group(2)); int topRowOfSpan; int bottomRowOfSpan; int incSize = 1; if(rowOffset > 0) { topRowOfSpan = cellAddr.row; bottomRowOfSpan = cellAddr.row + rowOffset; } else { topRowOfSpan = cellAddr.row + rowOffset; bottomRowOfSpan = cellAddr.row ; incSize = -1; } //System.out.println("move down: reference:"+cellAddr.reference+" topRowOfSpan:"+topRowOfSpan+ // " insertLocationRow:"+insertLocationRow+" bottomRowOfSpan:"+bottomRowOfSpan); if(topRowOfSpan <= insertLocationRow && bottomRowOfSpan > insertLocationRow) rowOffset += incSize; if(referenceMatcher.group(3).equals("[")) { referenceMatcher.appendReplacement(restultBuffer, "R["+rowOffset+"]C["+referenceMatcher.group(4)+"]"); } else { int colOffset = 0; String col = referenceMatcher.group(4); if(col != null && "".equals(col) == false) { colOffset = Integer.parseInt(col) - cellAddr.col; } referenceMatcher.appendReplacement(restultBuffer, "R["+rowOffset+"]C["+colOffset+"]"); } } else { int absoluteRow = Integer.parseInt(referenceMatcher.group(2)); if(absoluteRow >= insertLocationRow ) absoluteRow ++; if(referenceMatcher.group(3).equals("[")) { referenceMatcher.appendReplacement(restultBuffer, "R"+absoluteRow+"C["+referenceMatcher.group(4)+"]"); } else { referenceMatcher.appendReplacement(restultBuffer, "R"+absoluteRow+"C"+referenceMatcher.group(4)); } } } catch(NumberFormatException nfe) {} } referenceMatcher.appendTail(restultBuffer); updateReference = restultBuffer.toString(); } batchEntry.setId(String.format("%s/%s", worksheet.getCellFeedUrl().toString(), cellAddr.reference)); batchEntry.changeInputValueLocal(updateReference); BatchUtils.setBatchId(batchEntry, cellAddr.reference); BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE); // add the copy to the batch list batchRequest.getEntries().add(batchEntry); } // Submit the update Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM); service.setHeader("If-Match", "*"); CellFeed batchResponse = service.batch(new URL(batchLink.getHref()), batchRequest); service.setHeader("If-Match", null); 

Modified version https://developers.google.com/google-apps/spreadsheets/#updating_multiple_cells_with_a_batch_request

Problem: does not check lines in formulas

AppCell contains: row, col and reference