How to read data on Google Docs and write it back in Google Sheets (using app script)

Question

I'm a newbie with app script and got stuck with the below. I have a google sheet with a list of urls of google docs that have identical formatting. I want to be able to read the docs and write back to the google sheet in a new column, the text from a specific paragraph and row for each url. With the below code, I am able to read what I want to from the a doc but not exactly what I intend to do. I think I need to create a variable for each url on my sheet to start with. Can someone help with this please? Thanks very much in advance.

function doc_details() {      
  var doc = DocumentApp.openByUrl("this is where I enter the doc url");
  var body = doc.getBody();
  // select the table
  var table= body.getTables()[0];
// select the row
  var row = table.getRow(3);
  var res = row.getText();


  Logger.log(res);

}

RemcoE33 · Accepted Answer · 2021-09-09 12:40:45Z

1

Something like this would do:

Get the urls from the sheet.
Then for each url -> Do your code and push the result to the output array.
Write the array to the sheet in column B.

The code:

function doc_details() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetWithUrls = ss.getSheetByName('SheetWithUrls');
  const urls = sheetWithUrls.getRange('A2:A').getValues()
    .flat()
    .filter(url => url != '');

  const output = []

  urls.forEach(url => {
    const doc = DocumentApp.openByUrl(url);
    const table = doc.getBody().getTables()[0];
    const row = table.getRow(3);
    const res = row.getText();
    output.push([res])
  })

  //Starting from the second row / In the second column (B)
  sheetWithUrls.getRange(2,2,output.length,1).setValues(output);

}

answered Sep 9, 2021 at 12:40

RemcoE33

1,6111 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

arda0910 Over a year ago

Thanks very much! This resolved my issue. However, I now realised that about 1/4 of the docs I am running the script for are .docx saved in the google drive and/or the formatting was altered by users which causes my script to fail.

Collectives™ on Stack Overflow

How to read data on Google Docs and write it back in Google Sheets (using app script)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related