Appending a new row with only formulas using Sheets API v4

Question

I'm pretty new to Sheets API and get a lot of bugs.

I would like to append a new row to sheet based on last row. This would include copying the format and pasting formulas with an autofill but not the values.

Here what I've came up using app script. I'm sure I'm not using the best way so for the moment I've

retrieved formulas from range SUCCESS
tried using autoFillRequest to populate next row with 10 columns(just a try). FAILED

I've put in comment the getFormulas-like request and show you what I have for the moment with the autoFill request.

I get the following error:

Invalid JSON payload received. Unknown name "source_and_destination" at 'requests[0]': Cannot find field.

function insertNewRow(){
  var ssId = "my_spreadsheet_id"
  /*var params = {
    ranges: ['Feuille1!21:21'],
    valueRenderOption: 'FORMULA'
  };
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, params);
  var valueRange = Sheets.newValueRange();
  valueRange.majorDimension = "ROWS";
  valueRange.values = values.valueRanges[0].values;
  Logger.log(values.valueRanges[0].values[0].length);
  valueRange.range= 'Feuille1!22:22'
  //var req = Sheets.Spreadsheets.Values.update(valueRange , ssId, 'Feuille1!22:22', {
  //  valueInputOption: 'USER_ENTERED'
  //})*/
  var AFreq = Sheets.newAutoFillRequest();

  AFreq.range = Sheets.newGridRange();
  AFreq.range.startRowIndex = 1;
  AFreq.range.startColumnIndex = 0;
  AFreq.range.endRowIndex = 2;
  AFreq.range.endColumnIndex = 10;
  AFreq.range.sheetId = 0;

  AFreq.sourceAndDestination = Sheets.newSourceAndDestination();
  AFreq.sourceAndDestination.dimension = "ROWS";
  AFreq.sourceAndDestination.fillLength = 10;

  AFreq.sourceAndDestination.source = Sheets.newGridRange();
  AFreq.sourceAndDestination.source.startRowIndex = 0;
  AFreq.sourceAndDestination.source.startColumnIndex = 0;
  AFreq.sourceAndDestination.source.endColumnIndex = 10   
  AFreq.sourceAndDestination.source.endRowIndex = 1;
  AFreq.sourceAndDestination.source.sheetId = 0;

  var req = Sheets.newBatchUpdateSpreadsheetRequest();
  req.requests = [AFreq];
  Sheets.Spreadsheets.batchUpdate(req, ssId);
  }

Tell me if I'm wrong but I though about separating the tasks into multiple requests

grab the formulas
insert new row
copy/paste preceding fromat to new row
pasting formulas

Am I going in the right direction? Any help is greatly appreciated.

@I'-'I you mean why not just spreadsheetApp ? because if so I need to learn sheet-api and this is usually faster. I've done a spreadsheetApp script that is working but as the sheet is heavy it runs in about 2 or 5 minutes. — JSmith
– JSmith, Commented Oct 12, 2018 at 11:43
You're just making a newAutofill request. It should be inside request: "autoFill": { object(AutoFillRequest) }. I don't know how to get it in apps-script. But the hardcode is req.requests=[{ "autoFill": AFreq}].. EDIT: Nvm.. It should be var r= Sheets.newRequest(); r.autoFill = AFreq; req.requests=[r] — TheMaster
– TheMaster, Commented Oct 12, 2018 at 12:15
Start and end indices should not be the same (they are half open) — tehhowch
– tehhowch, Commented Oct 12, 2018 at 12:31
Look at your GridRange definitions. Indexes should be 0 base and half-open. E.g. 1:1 is nonsense (no dimension is selected by it) but 0:2 selects the first and second row/column — tehhowch
– tehhowch, Commented Oct 12, 2018 at 12:49

TheMaster · Accepted Answer · 2018-10-12 15:00:39Z

Issues:

Request object is missing in Request body.
AutoFillRequest has two union area fields, whereas exactly one is acceptable.
Empty range selection in GridRange.

Solution:

Fix syntax errors mentioned above
Used plain JSON request body to easily identify such errors

Sample Script:

function autoFill() {
  var ssId = 'my_spreadsheet_id';
  var req = {//request body    
    requests: [ //requests array    
      {//request Object
        autoFill: { //autoFill request
          //range OR sourceAndDestination;
          //equal to selecting Sheet1!A1:J10 and clicking autoFill from menu
          range: {//GridRange
            sheetId: 0,
            startRowIndex: 0,
            endRowIndex: 10, //end exclusive
            startColumnIndex: 0,
            endColumnIndex: 10,
          },
        },
      },
    ],
  };
  Sheets.Spreadsheets.batchUpdate(req, ssId);
}

Collectives™ on Stack Overflow

Appending a new row with only formulas using Sheets API v4

1 Answer 1

Issues:

Solution:

Sample Script:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Issues:

Solution:

Sample Script:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related