app-script/Google Docs to Spreadsheet and vice-versa

Question

Hello :) I wonder if there is a script/Google script that takes several Google Docs Documents with the same table inside,same structure(I mean table with first name,last name,age,classroom,final grade) and puts everything in a Google Spreadsheet table so that each row identify a student to have a collection of all the students in my year and if I modify a Google Document of a student it will modify in the spreadsheet and vice-versa if I modify the data in the speadsheet(like the classroom) it will modify in the Google Doc ? Thank you very much and I know that there is autoCrat but for each student I have a Google Doc file and I would be perfect to have a spreadsheet table.

Edit : I a very sorry if my question doesn't repect your policy I've tried this code :

var doc=DocumentApp.getActiveDocument();
  var body=doc.getBody();

  if(body)
  {
    var numChildren=body.getNumChildren();
    for(var i=0;i<numChildren;i++)
    {
      var child=body.getChild(i);
      if(child.getType()==DocumentApp.ElementType.TABLE)
      {
        var numrows = child.asTable().getNumRows();
        for(var j=0;j<numrows;j++)
        {
          var numcells=child.asTable().getRow(j).getNumCells();
          for(var k=0;k<numcells;k++)
          {
            var celltxt=child.asTable().getCell(j, k).editAsText().getText();

but to export after that in a spreadsheet I am blocked :( if you have an idea

This isn't the place to ask others to find a script for you but it's for asking for help if got stuck on writing/adapting an script. Please add a brief description of your search/research efforts including what you tried. — Rubén
Anyone who has taken the time to iron out propagation of changes amongst multiple linked documents is going to be charging you for their product. Consider what you really want: one place to store the data, and multiple ways to view or interact with that data. — tehhowch

deags deags · Accepted Answer · 2019-06-21T19:32:14

There are 2 roundabouts for this that may work for you:

-If you want a better setup from the start you can do an integration of Google Forms, Google Sites, and Google Sheets.

-If you want something that works with the info you have now, you can create a pivot sheet that pulls data via the function IMPORTHTML https://support.google.com/docs/answer/3093339?hl=en

There are other functions that may be of use: https://support.google.com/docs/table/25273?hl=en&ref_topic=9199554

Ideally if you want to set a whole system, you can directly have a Google Form fed the information into a concrete Google Sheets (and you can leave open the form's option to edit answers) and you can make multiple forms fed the same sheet that create different tabs for each form, and then have a table that combines them all. Since you can insert Docs, Sheets and Forms into a Google Sites then you can re-plan all this from the start.

Otherwise you can just try the import functions in a pivot sheet.

app-script/Google Docs to Spreadsheet and vice-versa

1 Answers