What's the easiest way to link Google Docs with Google Sheets?

Other Questions Google docs
1

I’m new to this and trying to make a simple connection between Google Docs and Sheets. Here’s what I’m aiming for:

function createDocument(data) {
  const personName = data.values[0];
  const doc = DocumentApp.create('New Document');
  doc.getBody().appendParagraph('Welcome, ' + personName);
  doc.saveAndClose();
}

I want to grab ‘personName’ from a sheet that’s created by a form. When the form data comes in, I want to make a new doc.

My process is:

  1. Form gets filled out
  2. Data goes to a spreadsheet
  3. I add some extra info to the spreadsheet
  4. Make a basic letter using the collected data

For example, I want to write something like:

doc.getBody().appendParagraph('Hi ' + personName + ',\n\nI really like your ' + item + '. Want to sell it?');

But I’m getting an error: ‘TypeError: Can’t read “values” of undefined.’ Any ideas on how to fix this and make it work?

2

I’ve worked on similar projects and found a reliable approach. Instead of creating docs directly from form submissions, set up a time-based trigger to run your script every few minutes. This avoids timing issues.

In your script, use SpreadsheetApp.getActiveSpreadsheet() to access the sheet data. Loop through new rows, create docs for each, then mark them as processed. Something like:

function processNewSubmissions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    if (data[i][5] !== 'Processed') {  // Assuming column F tracks status
      createDocumentFromRow(data[i]);
      sheet.getRange(i + 1, 6).setValue('Processed');
    }
  }
}

This method is more robust and can handle multiple submissions. It also gives you flexibility to add more processing steps later if needed. Remember to add error handling to log any issues during doc creation.

3

I’ve tackled similar projects before, and here’s what worked for me. Instead of trying to create the document directly from the form submission, set up a time-based trigger to run your script periodically. This approach is more reliable and less prone to errors.

In your script, use SpreadsheetApp.getActiveSpreadsheet() to access your sheet, then getDataRange() and getValues() to fetch the data. Loop through the rows, checking for any new entries that haven’t been processed yet. For each new entry, create a document using DocumentApp.create(), populate it with the data, and mark the row as processed in your sheet.

This method is more robust and allows you to handle multiple form submissions at once. It also gives you the flexibility to add additional processing steps if needed in the future. Remember to add error handling to catch and log any issues that might occur during document creation.

4

hey laura, i’ve dealt with this before. the issue is probably how you’re accessing the data. try this:

function onFormSubmit(e) {
  var data = e.values;
  var personName = data[1]; // assuming name is 2nd column
  var doc = DocumentApp.create('New Doc for ' + personName);
  doc.getBody().appendParagraph('hi ' + personName + '!');
  doc.saveAndClose();
}

set this to trigger on form submit. should work!