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

回答1:

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.

回答2:

The answer to the original questions was no, but did as stmcallister answered, it's not pretty nor fast, but works.

function preencherSmartSheet(){
  var v_sheetId = ##########; //Id planilha, definida no cronograma
  var dadosOrc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orçamento").getRange("A3:K").getValues();
  var rows = [], v_maxNivel = 0, v_linhasIds = [], calls = 0, payload, tempId;

  //Enviado ao smartsheet
  var dadosEnviar = accessToken; // Definido na aba Smartsheet API.gs
  dadosEnviar.Method = "post"; //post = incluir

  //Pega os dados da planilha Smartsheet
  var planilhaSmart = JSON.parse(UrlFetchApp.fetch("https://api.smartsheet.com/1.1/sheet/"+v_sheetId, accessToken));
  calls++;

  //Verifica qual a coluna das tarefas
  var idColuna = [];
  for(i in planilhaSmart.columns){
    if(planilhaSmart.columns[i].title == "Nome da tarefa"){
      idColuna[1] = planilhaSmart.columns[i].id;
    }else if(planilhaSmart.columns[i].title == "n°"){
      idColuna[0] = planilhaSmart.columns[i].id;
    }
  }

  //verifica maior nivel para começar
  for(i in dadosOrc){
    if(dadosOrc[i][10] > v_maxNivel){
      v_maxNivel = dadosOrc[i][10];
    }
  }

  //Formata as linhas em Objeto JSON
  for(v_nivelAtual = v_maxNivel; v_nivelAtual >= 0; v_nivelAtual--){
    for(i = 0; i < dadosOrc.length; i++){
      if(dadosOrc[i][10] == v_nivelAtual){
        if(v_nivelAtual == v_maxNivel){
          for(; i < dadosOrc.length; i++){
            if(dadosOrc[i][10] == v_nivelAtual){
              rows.push({"cells":[{"columnId" : idColuna[0].toString(), "strict" : false, "value" : i},
                                  {"columnId" : idColuna[1].toString(), "strict" : true, "value" : dadosOrc[i][0].toString()}]});
            }
          }
        }else{
          rows = [];
          tempId = i;
          for(; i < dadosOrc.length && dadosOrc[i][10] <= v_nivelAtual; i++){
            if(dadosOrc[i][10] == v_nivelAtual){
              rows.push({"cells":[{"columnId" : idColuna[0].toString(), "strict" : false, "value" : i},
                                  {"columnId" : idColuna[1].toString(), "strict" : true, "value" : dadosOrc[i][0].toString()}]});
            }
          }
        }

        if(v_nivelAtual < v_maxNivel){
          payload = {"toBottom":true, "rows":rows, "parentId" :  dadosOrc[tempId][1].toString()};
        }else{
          payload = {"toBottom":true, "rows":rows};
        }

        dadosEnviar.payload = JSON.stringify(payload);

        UrlFetchApp.fetch("https://api.smartsheet.com/1.1/sheet/"+v_sheetId+"/rows", dadosEnviar);
        calls++;
      }
    }


    planilhaSmart = JSON.parse(UrlFetchApp.fetch("https://api.smartsheet.com/1.1/sheet/"+v_sheetId, {headers:cabecalhoSmartsheet}));
    calls++;

    //Array com os id's da linhas pais, indice do array = linha do pai
    for(i in planilhaSmart.rows){
      v_linhasIds[planilhaSmart.rows[i].cells[0].value] = planilhaSmart.rows[i].id;
    }

    //Coloca no item 1 do array de dados do orçamento o id da linha pai das mesmas
    for(i in dadosOrc){
      if((dadosOrc[i][10]*1) == ((v_nivelAtual*1) - 1)){
        for(ii = i; ii >= 0; ii--){
          if(v_linhasIds[ii]){
            dadosOrc[i][1] = v_linhasIds[ii]
            ii = 0;
          }
        }
      }
    }
  }
  Logger.log(calls);
}