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

Other Questions Google docs
1

I’m a newbie trying to connect Google Docs and Sheets. I want to create a simple letter using data from a form response in Sheets. Here’s what I’ve got so far:

function createLetter(data) {
  var customerName = data.values[1];
  var doc = DocumentApp.create('New Letter');
  doc.getBody().appendParagraph('Hi ' + customerName);
  doc.saveAndClose();
}

The idea is to grab the name from a sheet that’s filled by a form. When the sheet updates, it should make a new doc. But I’m getting an error:

TypeError: Can't read 'values' of undefined (line 2)

I think it’s tied to the spreadsheet. The flow should be: form input > sheet data > edit sheet > make letter with collected info. For example:

doc.getBody().appendParagraph('Dear ' + customerName + ',\n\nI love your ' + itemName + '. Want to sell it?');

Any tips on fixing this and making it work? Thanks!

2

I’ve worked on similar projects and encountered this issue before. The error usually means that the event object, which contains the data from the form, isn’t being passed into the function properly. I recommend setting up an installable trigger for the form submission so that the function receives the event object. Then, you can access the form data using e.values. For instance, modify your function to use ‘e’ as a parameter and verify that it’s correctly passed when the submission occurs. Adjusting your data retrieval like this should solve the problem.

3

hey surfingwave, sounds like ur having trouble with the data. have u tried using the spreadsheetapp to grab the data directly? something like:

var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var customerName = data[1][0];

that might help u get the values ur looking for. good luck!

4

To link Google Docs with Sheets effectively, you might want to consider using the IMPORTRANGE function in Sheets to pull data from your form responses. Then, in your Google Apps Script, you can fetch this data more reliably. Here’s a quick example:

In your sheet, use: =IMPORTRANGE(“form_response_sheet_url”, “Sheet1!A1:B10”)

Then in your script:

function createLetter() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var customerName = data[1][0]; // Assuming name is in first column, second row

var doc = DocumentApp.create(‘New Letter’);
doc.getBody().appendParagraph('Dear ’ + customerName + ‘,’);
doc.saveAndClose();
}

This approach should be more stable and easier to maintain. Remember to authorize IMPORTRANGE when you first use it.