Copy a range from worksheet to google doc by replace text with table format-CodePudding

I want to copy a range cell from a worksheet to a google doc, keeping a table format

In a google doc template I added a text to be replaced.

{{TablaDetalle}}

I have a range for replace {{TablaDetalle}} and I get as

Item	Equipo	Nro Serie	Sitio
1	AA	11	Sitio A
2	AA	22	Sitio A
3	UPS	88	Sitio A
4	UPS	99	Sitio A

A part of my code is

var hoja= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base Datos")
var wTablaDetalle=hoja.getRange("A1:D5").getDisplayValues()

function crearCoti() {
  //create a new document based on template and store on a temp folder

  var templateDoc=DriveApp.getFileById(wTemplateId); // 
  var blobTemplate=templateDoc.getBlob()

  var folderNewCoti=DriveApp.getFolderById(wfolderNewCoitId);
  var folderTemp=DriveApp.getFolderById(wfolderTempId);
  

  var copyArchivoPlantilla=templateDoc.makeCopy(folderTemp)
  
  // get id from new copy template created and open it
  var copyId= copyArchivoPlantilla.getId();
  var wcopyArchivoPlantilla=DocumentApp.openById(copyId)
    
  wcopyArchivoPlantilla.getBody().replaceText("{{TablaDetalle}}",wTablaDetalle)

of course, the text that be copied into google doc is

Item,Equipo,Nro serie,Sitio,1,AA,11,Sitio A,2,AA,22,Sitio A,3,UPS,88,Sitio A,4,UPS,99,Sitio A

Is there a way to copy the bellow data as a table or with any format ? By the way, the range to copy can get more or less rows, but always the same columns

I Wait for yours comments and thaks in advance

Omar

CodePudding user response：

Here is an example of how to insert a table by replacing the marker paragraph.

I have provided 2 options, 1) looping through each paragraph looking for the specific text and 2) using body.findText()

Code.gs

function test() {
  try {
    let doc = DocumentApp.getActiveDocument();
    let body = doc.getBody();
    let data = [['Item','Equipo','Nro serie','Sitio'],
                [1,'AA',11,'Sitio A'],
                [2,'AA',22,'Sitio A'],
                [3,'UPS',88,'Sitio A'],
                [4,'UPS',99,'Sitio A']];
    let paras = body.getParagraphs();
    paras.some( para => {
        if( para.getText() === '{{TablaDetalle}}' ) {
          let index = body.getChildIndex(para);
          let table = body.insertTable(index,data);
          for( let i=0; i<table.getNumRows(); i   ) {
            let row = table.getRow(i);
            for( let j=0; j<row.getNumCells(); j   ) {
              let cell = row.getCell(j);
              if( i === 0 ) {
                cell.setBackgroundColor('#FFFF00');
              }
              cell.getChild(0).asParagraph().setAlignment(DocumentApp.HorizontalAlignment.CENTER);
            }
          }
          body.removeChild(para);
          return true;
        }
        return false;
      }
    );
  }
  catch(err) {
    console.log(err);
  }
}

As an alternative using the body.findText()

function test() {
  try {
    let doc = DocumentApp.getActiveDocument();
    let body = doc.getBody();
    let data = [['Item','Equipo','Nro serie','Sitio'],
                [1,'AA',11,'Sitio A'],
                [2,'AA',22,'Sitio A'],
                [3,'UPS',88,'Sitio A'],
                [4,'UPS',99,'Sitio A']];
    let element = body.findText('{{TablaDetalle}}');
    if( element ) {
      element = element.getElement().getParent();
      let index = body.getChildIndex(element);
      let table = body.insertTable(index,data);
      for( let i=0; i<table.getNumRows(); i   ) {
        let row = table.getRow(i);
        for( let j=0; j<row.getNumCells(); j   ) {
          let cell = row.getCell(j);
          if( i === 0 ) {
            cell.setBackgroundColor('#FFFF00');
          }
          cell.getChild(0).asParagraph().setAlignment(DocumentApp.HorizontalAlignment.CENTER);
        }
      }
      body.removeChild(element);
    }
    else {
      console.log("not found");
    }
  }
  catch(err) {
    console.log(err);
  }
}

Reference