Get text from google sheet cell to a google doc

Question

I have a google sheet which we create a new version every week with the sheet name updated. In this sheet we have a cell which has notes.The cell is fixed. How can I pull data from all sheets which lets says starts with Reporting Weekly gets the text from that cell and push it to a google doc.

question from:https://stackoverflow.com/questions/65599380/get-text-from-google-sheet-cell-to-a-google-doc

Answer 1

function copyNotes() {
  const prefix="Report Weekly";
  const fldrid="";//report folder id
  const name=Utilities.formatString('ReportSummary:%s',Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "E-yy.MM.dd.HH.mm.ss"));//you may wish to change name formatting for the new document
  const fldr=DriveApp.getFolderById(fldrid);
  const fileid=DocumentApp.create(name).getId();//creating the document
  const doc=DocumentApp.openById(fileid);//open document
  const notecell='A1';//The cell where the notes are found
  const ss=SpreadsheetApp.getActive();
  const shts=ss.getSheets().filter(s=>s.getName().startsWith(prefix));//filter out unwanted sheets
  shts.forEach(sh=>{doc.getBody().appendParagraph(sh.getRange(notecell).getValue())});//read notecells of all sheets and append to document
  doc.saveAndClose();//save document in root
  Drive.Files.update({"parents": [{"id": fldr.getId()}]}, fileid);//move to correct folder
}

Note: You will need to enable the advanced Drive API inorder to run this function.