Search code examples
google-sheetsgoogle-docs

Transferring a Google spreadsheet to a Google document

Help me change the script so that the table from Google Sheets transfers the table to Google Docs only those rows and columns where column G has the value true? And how can you put the table in the right place? And how can I put the table in the right place (%table% in the template file)?

template

table

function actGeneration() {
  // Перенос данных
  const docFile = DriveApp.getFileById("183tq6LvgBhzfbWUpNCWAV0X6Tn49yJ1KGuwKTfiSQJs"); //шаблон
  const tempFolder = DriveApp.getFolderById("1AgokTJRgvuMdVO_X3sO-S22Nvp5s0i2U"); // директория новых актов
  const tempFile = docFile.makeCopy(tempFolder); // копия шаблона
  const tempDocFile = DocumentApp.openById(tempFile.getId()); // открываем созданную копию
  const body = tempDocFile.getBody(); // тело(текст) файла копии
    
  // Фиксированные позиции
  var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //получаем текст с открытого окна таблицы
  
  var space = Array()
  space[0] = list.getRange(6, 9).getValue(); // дата
  space[1] = list.getRange(2, 9).getValue(); // номер договора
  space[2] = list.getRange(4, 9).getValue(); // имя заказчика
  space[3] = list.getRange(3, 9).getValue(); // адресс заказчика
  space[4] = list.getRange(5, 9).getValue(); // цена заказа
  space[5] = list.getRange(6, 9).getValue(); // дата договора
  space[6] = list.getRange(5, 10).getValue(); // цена прописью
  
  body.replaceText("%дата%", space[0]);
  body.replaceText("%номер%", space[1]);
  body.replaceText("%заказчик%", space[2]);
  body.replaceText("%адрес%", space[3]);
  body.replaceText("%стоимость%", space[4]);
  body.replaceText("%дата.договора%", space[5]);
  body.replaceText("%пропись%", space[6]);

  // Перенос таблицы
  var sheet = list;
  var range = sheet.getRange(1, 1, 64, 6).getDataRegion(SpreadsheetApp.Dimension.ROWS);
  var values = range.getValues();
  var backgroundColors = range.getBackgrounds();
  var styles = range.getTextStyles();
  var aligns = range.getHorizontalAlignments()

  var colWidth = [];  // Added
  for (var col = 1; col <= 6; col++) {  // Added
    colWidth.push(sheet.getColumnWidth(col) * 3 / 4);
  }

  // Position to paste data in Google Docs
  var table = body.appendTable(values);
  table.setBorderWidth(1);

  colWidth.forEach(function(e, i) {table.setColumnWidth(i, e)});  // Added

  for (var i = 0; i < table.getNumRows(); i++) {
      for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
        var obj = {};
        obj[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
        obj[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
        obj[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = aligns[i][j];
        if (styles[i][j].isBold()) {
          obj[DocumentApp.Attribute.BOLD] = true;
        }
        table.getRow(i).getCell(j).setAttributes(obj);
    }
  }
  
  tempDocFile.saveAndClose();
}

We managed to move the table, but not as expected.

Solution

  • There are two parts to the question:

    • only those table rows where column G has the value = true
    • put the table in the right place (%table% in the template file)

    The answer consists of two scripts:

    • buildMergeTable()

      • creates a new "Merge Table" that can be used for the Document merge
      • "unchecked" rows are deleted
      • "Total" formula has been updated

    • mergeAndInsertTable() - based on the OP's code

      • uses the "Merge Table" for inserting the "Table"
      • inserts the "Table" at %table%
      • deletes %table% afterwards

    The scripts must be run in sequence:

    1. buildMergeTable()
    2. mergeAndInsertTable()
    • The scripts can be combined but I have separated them so that the purpose and process of each script is more clear.

    only rows where value = true - function buildTable()

    The main steps are:

    • copy the original sheet = "Лист2 (копия)"
      • name the copy = "MergeTable""
    • loop Column G === true
      • maintain a rowCounter to keep track of the actual row
      • if true, then increment the rowCounter
      • if not true, then delete the row
    • update the "Total" formula
      • deleting rows will corrupt the formula:
        • BEFORE: =SUM(F59,F52,F43,F37,F30,F21,F12,F9)
        • AFTER: =SUM(#REF!,#REF!,#REF!,#REF!,F7,#REF!,#REF!,F4)
      • use replaceAll('#REF!,', '')
      • update the revised formula: setFormula(newFormula)

    put the table in the right place (%table%)

    • identify the index for the placeholder
      • var tablePlaceHolder= body.findText("%table%")
      • var element=tablePlaceHolder.getElement()
      • var childIndex= body.getChildIndex(element.getParent())
    • delete the placeholder
      • body.getChild(childIndex).asText().setText('')
    • insert the table at the index
      • var table = body.insertTable(childIndex,values)
    function buildMergeTable() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var originalSheetName = "Лист2 (копия)"
  var originalSheet = ss.getSheetByName(originalSheetName);

  // Copy the original sheet
    var targetSheetName = "MergeTable"
    originalSheet.copyTo(ss).setName(targetSheetName);
    var targetSheet = ss.getSheetByName(targetSheetName)

  // delete the rows that aren't checked
    var numRows = 64 // fixed
    var lastCol=7 // column G
    var range = targetSheet.getRange(1,1,numRows,lastCol)
    // Logger.log("DEBUG: the data range = "+range.getA1Notation())
    var values = range.getValues()

    // make a counter for the row number
    let rowCounter = 1
    for (i=0;i<numRows;i++){
      if (values[i][(lastCol-2)] === true){ // zero-based tests column G
        // Logger.log("DEBUG: TRUE i="+i)
        rowCounter++
      }
      else{
        // Logger.log("DEBUG: FALSE, so delete row: i="+i+", row = "+rowCounter)
        targetSheet.deleteRow(rowCounter); // NOT zero-based
      }
    }

  // update the Total cell
    // get the last row and the cell reference
    var bVals = targetSheet.getRange("B1:B").getValues();
    var bLast = bVals.filter(String).length;
    var totalCellRange = targetSheet.getRange(bLast,6)
    // Logger.log("DEBUG: the Total cell range = "+totalCellRange.getA1Notation())    

    // get the formula
    var totalCellFormula = totalCellRange.getFormula()
    // remove #REF! from formula
    var newFormula = totalCellFormula.replaceAll('#REF!,', '')

    // update formula 
    totalCellRange.setFormula(newFormula)
}


function mergeAndInsertTable() {
  // Перенос данных
  const docFile = DriveApp.getFileById("1fM6_GfqI5_pze8W_aHWqVSTKSTeU_SHHTB020lgxf9w"); //шаблон
  const tempFolder = DriveApp.getFolderById("1hhMDnlMzm2B7rzp3Qnehx8sa_gM2B5Yy"); // директория новых актов
  const tempFile = docFile.makeCopy(tempFolder); // копия шаблона
  const tempDocFile = DocumentApp.openById(tempFile.getId()); // открываем созданную копию
  const body = tempDocFile.getBody(); // тело(текст) файла копии
    
  // Фиксированные позиции
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var mergeSheetName = "Лист1"
  var list = ss.getSheetByName(mergeSheetName) //получаем текст с открытого окна таблицы
   
  var space = Array()

  space[0] = list.getRange(6, 9).getValue(); // дата// date
  space[1] = list.getRange(2, 9).getValue(); //номер договора // contract number
  space[2] = list.getRange(4, 9).getValue(); // имя заказчика // customer name
  space[3] = list.getRange(3, 9).getValue(); // адресс заказчика // customer address
  space[4] = list.getRange(5, 9).getValue(); // цена заказа // order price
  space[5] = list.getRange(6, 9).getValue(); // дата договора// contract date
  space[6] = list.getRange(5, 10).getValue(); // цена прописью // price in writing
  // Logger.log("DEBUG:\nDate:"+space[0]+" ("+list.getRange(6, 9).getA1Notation()+")\nContract#:"+space[1]+" ("+list.getRange(2, 9).getA1Notation()+")\nCustomer:"+space[2]+" ("+list.getRange(4, 9).getA1Notation()+")\nCustomer address:"+space[3]+" ("+list.getRange(3, 9).getA1Notation()+")\nOrder price:"+space[4]+" ("+list.getRange(5, 9).getA1Notation()+")\nContract Date:"+space[5]+" ("+list.getRange(6, 9).getA1Notation()+")\nWritten price:"+space[6]+" ("+list.getRange(5, 10).getA1Notation()+")")
  //return
  body.replaceText("%дата%", space[0]);
  body.replaceText("%номер%", space[1]);
  body.replaceText("%заказчик%", space[2]);
  body.replaceText("%адрес%", space[3]);
  body.replaceText("%стоимость%", space[4]);
  body.replaceText("%дата.договора%", space[5]);
  body.replaceText("%пропись%", space[6]);

  // Перенос таблицы
  
  // Get Google Sheet data
  var tableSheetName = "MergeTable"
  var tableSheet = ss.getSheetByName(tableSheetName)

  // get the last row and the cell reference
  var bVals = tableSheet.getRange("B1:B").getValues();
  var bLast = bVals.filter(String).length;
  var range = tableSheet.getRange(1, 1, bLast, 6).getDataRegion(SpreadsheetApp.Dimension.ROWS);
    
  var values = range.getValues();
  var backgroundColors = range.getBackgrounds();
  var styles = range.getTextStyles();
  var aligns = range.getHorizontalAlignments()

  var colWidth = [];  // Added
  for (var col = 1; col <= 6; col++) {  // Added
    colWidth.push(tableSheet.getColumnWidth(col) * 3 / 4);
  }
  // Logger.log(colWidth)


  // Position to paste data in Google Docs

  var tablePlaceHolder= body.findText("%table%")
  var element=tablePlaceHolder.getElement()
  var childIndex= body.getChildIndex(element.getParent())
  // delete the table placeholder
  body.getChild(childIndex).asText().setText('')
  var table = body.insertTable(childIndex,values)
  table.setBorderWidth(1);
  colWidth.forEach(function(e, i) {table.setColumnWidth(i, e)});  // Added

  for (var i = 0; i < table.getNumRows(); i++) {
      for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
        var obj = {};
        obj[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
        obj[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
        obj[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = aligns[i][j];
        if (styles[i][j].isBold()) {
          obj[DocumentApp.Attribute.BOLD] = true;
        }
        table.getRow(i).getCell(j).setAttributes(obj);
    }
  }
  
  tempDocFile.saveAndClose();
}

    SAMPLE

    sample