CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
Vash Kris
Vash Kris

Reputation: 13

้How to Copy Some Content of Google Document Table to Google Spreadsheet By Google App Script

Are there any way to choose some content in the Google Document Table and copy to the Google Spreadsheet by the Google App Script

Following with my photo idea

that I would like to copy some content in the red box / blue box to the Google Spreadsheet including with the data in column name : level , Due Date 1, Due Date 2

I've started with the following code outline:

function Tables() {
var doc = DocumentApp.getActiveDocument().getBody();  
var tables = doc.getTables()[1]; 
for (var i = 0; i < tables.getNumRows(); ++i) {
  var cell = tables.getRow(i);
  var text =cell.getText();
  Logger.log(text);
} 
}

Upvotes: 1

Views: 1238

Answers (1)

Tanaike
Tanaike

Reputation: 201613

Although I cannot see your sample Spreadsheet, from your sample Google Document and sample output image, I believe your goal as follows.

  • You want to retrieve the values from the 1st table in Google Document and put the values to Google Spreadsheet by converting the values as follows.

    • From

      enter image description here

    • To

      enter image description here

Modification points:

  • In your script, 2nd table is retrieved. In your sample Document, an error occurs.
  • In your case, I think that it is required to retrieve the values from each cell. But in your script, the values are directly retrieved from the rows.
  • In your script, the values are only retrieved from the rows. In order to achieve your goal, it is required to convert the retrieved values from Document for putting to Spreadsheet.

When above points are reflected to a script, it becomes as follows.

Modified script:

Please copy and paste the following script to the script editor and set the variables and run the script. About the variable of excludeTexts, in this case, these values are from your sample Google Document. When you change the exclude text line in the table of Document, please modify the values.

function myFunction() {
  const documentId = "###";  // Please set Document ID.
  const spreadsheetId = "###";  // Please set Spreadsheet ID of the destination Spreadsheet.
  const sheetName = "Sheet1";  // Please set the sheet name of the destination sheet.
  const excludeTexts = ["Part A:", "Part B:", "Part C:"];  // Please set the exclude texts.

  // 1. Retrieve values from 1st table from Document and create an array for putting to Spreadsheet.
  // const body = DocumentApp.getActiveDocument().getBody(); // If you copy and paste the script to the container-bound script of Document, you can use this line instead of "DocumentApp.openById(documentId).getBody()".
  const body = DocumentApp.openById(documentId).getBody();
  const table = body.getTables()[0];
  let values = [];
  for (let r = 0; r < table.getNumRows(); r++) {
    const cell = table.getRow(r);
    const row = [];
    for (let c = 0; c < cell.getNumCells(); c++) {
      const text = cell.getCell(c).getText().trim();
      row.push(r > 0 && c == 1 ? text.split("\n").map(e => e.trim()).filter(e => !excludeTexts.includes(e)) : text);
    }
    values = values.concat(Array.isArray(row[1]) ? row[1].map((e, i) => i == 0 ? [row[0], e, ...row.slice(2)] : ["",e,...Array(row.slice(2).length).fill("")]) : [row]);
  }
  console.log(values)

  // 2. Put the created array to Spreadsheet.
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
  • When above script is run for your Google Document, the sample situation at the top images is obtained.

Note:

  • This sample script can be used for your sample Google Document. If the structure of your Document is changed, the script might be required to be modified. So please be careful this. When you test this script, please use your sample Google Document.

References:

Upvotes: 1

Related Questions