CODEWITHSUNDEEP
google-sheetsappendgoogle-sheets-api
Serob_b
Serob_b

Reputation: 1059

Google Sheets api append method is not adding a line after the last row

It is supposed that values.append method should add a line when it meets an empty row starting from the first row of the defined range. If there is no gaps between existing rows, then it should add a line in the end of the defined range table.

In my case I use the following values to append a row after for example 22-nd row (using python), when all rows till 30 are full:

range="MySheet!A22",
valueInputOption="USER_ENTERED",
insertDataOption="INSERT_ROWS",
body={"majorDimension": "ROWS", "values": [['asd', 'asd2']]}

But I got it inserted after the headings (9-th row) with the style of the heading. And in response:

"tableRange": "MySheet!A1:C9"
"updatedRange": "MySheet!A10:B10"

enter image description here

MySheet!A13 or MySheet!A14 or MySheet!A14:C14 didn't work also to enter a row after 13/14-th rows.

This behavior is out of the used programming language/environment, as I get the same result using their web interface test Try it!.

-- UPDATE --

Here is a test sheet. Better if you make a copy and run your tests on it.

And here is the test data that I want to append. You can use the same form only replacing spreadsheetId with your copy one. You can see that the range is set to test which means that it has to search all the tables inside the sheet, find the last one, and add the data after it. But data is added on the 10-th row.

It behaves as expected (adds line at the end) if I:

  1. Delete dummy image or remove its cell (A2:C4)
  2. Delete some text or remove its cell (I2:I6)
  3. Delete one of the columns from C to G
  4. Remove link on the URL in cells B10 and B11 (if I remove only B10 link, data is added in between of two rows)enter image description here

Please support the opened issue by staring it: Issue Tracker Bug Report

Upvotes: 2

Views: 2312

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Issue and workaround:

When the method of "spreadsheets.values.append" is used, I have experienced the same situation with you. In that case, I thought that it might be the current specification at Google side or a bug. So, as a workaround, I have changed the method from "spreadsheets.values.append" to "spreadsheets.batchUpdate". In my environment, when AppendCellsRequest of the method of "spreadsheets.batchUpdate" was used, I confirmed that the issue could be resolved.

When this workaround is reflected to a script, it becomes as follows.

Sample curl command:

curl --request POST \
  'https://sheets.googleapis.com/v4/spreadsheets/[SpreadsheetId]:batchUpdate' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"requests":[{"appendCells":{"sheetId":[sheetId],"rows":[{"values":[{"userEnteredValue":{"stringValue":"sample1"}},{"userEnteredValue":{"stringValue":"sample2"}}]}],"fields":"userEnteredValue"}}]}' \
  --compressed
  • When you want to test this using "Try tis API", you can also use here.

Result:

When I used above request for your sample Spreadsheet, the following result is obtained. You can see the values are appended to the sheet of "test".

enter image description here

References:

Added:

From your following replying,

I don't know anything about Web Apps created by Google Apps Script as the wrapper API. I'll look on it. Can you provide some docs?

You can see the sample situations at https://github.com/tanaikech/taking-advantage-of-Web-Apps-with-google-apps-script#samplesituations But, I think that if you want to use this method for your situation, it is required to create the script for achieving your goal. So as the additional information, for your situation, I would like to introduce the method using Web Apps as the wrapper API.

Usage:

1. Create sample script.

Please copy and paste the following script to the script editor of Google Spreadsheet which is your sample Spreadsheet.

function doPost(e) {
  const sheetName = "test";  // This is the sheet name in your sample Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const values = JSON.parse(e.postData.contents);
  sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
  return ContentService.createTextOutput("Done.");
}

2. Deploy Web Apps.

The detail information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
  2. Please click "Select type" -> "Web App".
  3. Please input the information about the Web App in the fields under "Deployment configuration".
  4. Please select "Me" for "Execute as".
    • This is the important of this workaround.
  5. Please select "Anyone" for "Who has access".
    • In this case, the user is not required to use the access token. So please use this as a test case.
    • Of course, you can access to the Web Apps using the access token. In that case, please set to Anyone with Google account or Only myself. When you want to use the access token, please include the scopes for Drive API like https://www.googleapis.com/auth/drive.readonly and https://www.googleapis.com/auth/drive.
  6. Please click "Deploy" button.
  7. Copy the URL of Web App. It's like https://script.google.com/macros/s/###/exec.

3. Testing.

As the test of above Web Apps, you can test this using a curl command. In that case, please replace the URL with your URL. At above flow, the access token is not required to be used. So when the following curl command is run, the values of sample1 and sample2 are put to the columns "A" and "B" at the 1st empty row of test sheet.

$ curl -L -d '[["sample1", "sample2"]]' "https://script.google.com/macros/s/###/exec"
  • When this curl command is run, when your setting is correct, Done. is returned.

References:

Upvotes: 3

Related Questions