CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
assembler
assembler

Reputation: 3302

How to insert column after the last column with Sheets API?

I have this spreasheet with a single table:

spreadsheet structure

Calling this: POST 'https://sheets.googleapis.com/v4/spreadsheets/{my_id}/values/Sheet1!E1:append?valueInputOption=USER_ENTERED' with POST method and with these headers:

Authorization: Bearer {valid_auth_token}
Accept: application/json
Content-Type: application/json

and this body:

{
    "majorDimension": "COLUMNS",
    "values": [
        ["Header", "Hello"]
    ]
}

Will append Header, Hello as a column in E. My question is: how can I append new columns after the last column without specifiying the new range (E1 this case)? Is there any way to do so?

With this call: https://sheets.googleapis.com/v4/spreadsheets/{my_id} I am able to get the Sheets name, but unable to get the ranges, how can I know the range?

Upvotes: 3

Views: 2164

Answers (1)

Tanaike
Tanaike

Reputation: 201378

  • You want to put the values of Header and Hello to the next column of the last column on the Spreadsheet without directly giving the range.
  • You want to achieve this using Sheets API.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, in the current stage, there are no methods in the Sheets API for directly retrieving the data range. Although there are the method of spreadsheets.values.append and the AppendCellsRequest of the method of spreadsheets.batchUpdate, these can be used for putting the values to the next row of the last row on the sheet. It's not the column. Also in this case, the data range cannot be retrieved. So in order to achieve above your goal, I would like to propose the following workaround.

In this workaround, the Web Apps is used as the wrapper. The client accesses to the Web Apps, and the Web Apps retrieves the data range and put the values to the next column of the last column on the Spreadsheet. In this case, the range is not required to be set when it accesses to the Web Apps.

Usage:

The usage of this workaround is as follows.

1. Create new project of Google Apps Script.

Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

2. Copy and paste script.

Please copy and paste the following script.

function doPost(e) {
  var obj = JSON.parse(e.postData.contents);
  var sheet = SpreadsheetApp.openById(obj.spreadsheetId).getSheetByName(obj.sheetName);
  var values = obj.values;
  sheet.getRange(1, sheet.getLastColumn() + 1, values.length, values[0].length).setValues(values);
  return ContentService.createTextOutput("ok");
}
  • This sample script puts the values to the next column of the last column on the Spreadsheet.

3. Deploy Web Apps.

  1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
  2. Select "Me" for "Execute the app as:".
  3. Select "Only myself" for "Who has access to the app:".
    • When "Anyone, even anonymous" is set to "Who has access to the app:", the access token is not required to be used.
  4. Click "Deploy" button as new "Project version".
  5. Automatically open a dialog box of "Authorization required".
    1. Click "Review Permissions".
    2. Select own account.
    3. Click "Advanced" at "This app isn't verified".
    4. Click "Go to ### project name ###(unsafe)"
    5. Click "Allow" button.
  6. Click "OK".
  7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
    • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

4. Sample curl.

In order to test to access to the Web Apps, please use the following curl command.

curl -L \
  -H "Authorization: Bearer ###" \
  -d '{"spreadsheetId": "###", "sheetName": "Sheet1", "values": [["Header"], ["Hello"]]}' \
  "https://script.google.com/macros/s/###/exec"
  • Please set the URL of Web Apps retrieved above and the Spreadsheet ID and the sheet name.
  • In this case, the range is not required to be included in the request body. Because the data range is retrieved at the script of Web Apps.
  • When above curl command is run, the values of "Header" and "Hello" are put to the next column of the last column on the sheet of "Sheet1".

Note:

  • If an authorize error occurs, for example, please set "Anyone, even anonymous" to "Who has access to the app:". In this case, the access token is not required to be used.
  • Or, please include the scope of https://www.googleapis.com/auth/drive.readonly and/or https://www.googleapis.com/auth/drive to the access token.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 3

Related Questions