Appending a new row with only formulas using Sheets API v4

0
votes

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

  1. grab the formulas
  2. insert new row
  3. copy/paste preceding fromat to new row
  4. pasting formulas

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

1
javascriptgoogle-apps-scriptgoogle-sheets-api
@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
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
Start and end indices should not be the same (they are half open) - tehhowch
@I'-'I I can't find autoFillanywhere in the object. - JSmith
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

1 Answers

1
votes

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);
}

References: