Yo! This is my code that uses the last line of a spreadsheet to generate a google docs, in addition, it places a link to this document in the source spreadsheet. Is it possible to create code that does this for all rows in a worksheet? I'm having trouble using .range() to read the lines individually. The reason for this is simply to maintain and update generated versus updated data.

In advance, thank you!

var app=SpreadsheetApp.openById("Google ID");
var sheet=app.getSheetByName('Respostas ao formulário 1')


function autoFillLastRow() {

  var range=sheet.getDataRange().getValues();
  var elem = range.pop();

  //e.values is an array of form values
  var timestamp = elem[0];

  //conversor de datas
  let data = new Date(timestamp);
  let dataFormatada = ((data.getDate() ))   "/"   ((data.getMonth()   1))   "/"     data.getFullYear(); 

  var firstName = elem[1];
  var lastName = elem[2];
  var title = elem[3];
  
  //file is the template file, and you get it by ID
  var file = DriveApp.getFileById('Google Drive ID'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live in
  //file.makeCopy will return a Google Drive file object
  var folder = DriveApp.getFolderById('Google Drive ID')
  var copy = file.makeCopy(dataFormatada   ', '   firstName, folder); 
  
  //Once we've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything we need to change is in the body, we need to get that
  var body = doc.getBody(); 
  
  //Then we call all of our replaceText methods
  body.replaceText('{{Nome}}', firstName); 
  body.replaceText('{{Sobrenome}}', lastName);  
  body.replaceText('{{Título}}', title); 
  
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 

  //edit cell
  var coluna = 5;
  var linha = sheet.getLastRow();
  var docId = doc.getId();
  sheet.getRange(linha, coluna,1,1).setValue("https://docs.google.com/document/d/"   docId);


  }

CodePudding user response：

Create document for every row of spreadsheet

   function createDocforEveryRowofSpreadsheet() {
      var ss = SpreadsheetApp.openById("Google ID");
      var sh = ss.getSheetByName('Respostas ao formulário 1')
      var file = DriveApp.getFileById('Google Drive ID');
      var folder = DriveApp.getFolderById('Google Drive ID')
      var [hA,...vs] = sh.getDataRange().getValues();
      vs.forEach((r,i) =>  {
        var ts = r[0];
        let data = new Date(ts);
        let dataFormatada = Utilities.formatDate(new Date(ts), Spreadsheet.getSpreadsheetTimeZone(), "dd/MM/yyyy")
        var firstName = r[1];
        var lastName = r[2];
        var title = r[3];
        var copy = file.makeCopy(dataFormatada   ', '   firstName, folder);
        var doc = DocumentApp.openById(copy.getId());
        var body = doc.getBody();
        body.replaceText('{{Nome}}', firstName);
        body.replaceText('{{Sobrenome}}', lastName);
        body.replaceText('{{Título}}', title);
        doc.saveAndClose();
      })
    }