google-apps-script google-sheets google-docs

Can I export my data to a Google Doc from Google Sheets using Apps Script?

My question may be a little hard to explain, but hopefully someone knows how to help! The co-op that I live in uses this google sheet to create our grocery list to shop for the week.

Currently, the user uses the check boxes next to items in the List Filler sheet to send them over to the grocery list, found on a separate sheet, where only checked items appear from the List Filler to hopefully help the shoppers get a clearer view of what needs to be shopped for.

My problem is that my current grocery list is a little hard to look at and makes shopping difficult for those who have to swipe through their phone to see the items. I was hoping on using DocumentApp and AppScripts to take the data from the grocery list and import it into a google doc that can be easily printed whenever we have to shop.

I've seen some tutorials/articles on how to do this directly, but their data often looks quite different to mine, and I have been having a lot of trouble trying to translate.

I am not sure where to start here, so any help would be greatly appreciated! Thank you :))

(The sheet is not in a shared drive)

Solution

Here is an example of copying a Spreadsheet table to a Google Doc.

I use a place holder {{table1}} to show where I want the table. Note there needs to be at least 1 paragraph after this placeholder to remove the text.

Here is my example spreadsheet.

And the Doc before the table has been inserted

In the Google Doc script editor I have this script.

function addTable() {
  try {
    let doc = DocumentApp.getActiveDocument();
    let spread = SpreadsheetApp.openById("1xxxxxxxxxxxxxxxxxxxxxxxxxxA");
    let sheet = spread.getSheetByName("Sheet1");
    let values = sheet.getDataRange().getDisplayValues();
    let body = doc.getBody();
    let paras = body.getParagraphs();
    let index = paras.findIndex( para => para.findText("{{table1}}"));
    index = body.getChildIndex(paras[index]);
    body.insertTable(index,values);
    paras[index].removeFromParent();
  }
  catch(err) {
    console.log(err);
  }
}

The Doc after running the script

Reference