google-apps-scriptsmartsheet-apismartsheet-api-1.1

Setting a batch of parents/sibblings in one call


I'm building an Google Script that automatically writes a Google Spreadsheet to Smartsheet, but with the parents/sibbling already formated, no need for manual indeting, since I already have to do this in the Spreadsheet, trough a numbered column.

I managed to write to the Smartsheet, but I can't seem to figure out how to send a batch (600+) of lines with parents/sibblings set, which are in the same urlFetch call.

Some questions which should solve my problem:

Is it possible to determine/specify the "rowId" that I'm about to include?

Is it possible to set "parentRowNumber" when providing the row?

Is it possible any other way to batch insert rows with parents without knowing the "rowId"?

Since I haven't found [google-apps-script] + [smartsheet-api] here yet, here's how to insert a row (working code):

function adcionarLinhaSmartSheet(){
  //Static for testing
  var rows = [];
  rows[0] = {};
  rows[0].cells = [];
  rows[0].cells[0] = {};
  rows[0].cells[0].columnId = "[ROW_ID]";
  rows[0].cells[0].value = 14;
  rows[0].cells[0].strict = false;

  var payload = {"toBottom":true, "rows":rows};

  var dadosEnviar = {headers:{Authorization:"Bearer [ACCESS_TOKEN]", "content-type":"application/json"}, "Method":"post", "payload":JSON.stringify(payload)};

  var planilhaSmart = UrlFetchApp.fetch("https://api.smartsheet.com/1.1/sheet/[SHEET_ID]/rows", dadosEnviar);
}

Solution

  • You can insert multiple rows with a single POST to the Smartsheet API, but all the rows in that call will be added at the same heirachal level in your sheet. So, to create a sheet using rows with parent/children relationships you could do the following:

    First, make one call to insert all the parent level rows into your sheet.

    Then, using the rowIds of the newly created rows, make additional calls to POST all the children rows for each parent. You'll need to make a separate call for each parent row's group of children rows.