Create Google Docs from Google Sheets using Apps Script

If you're a blogger, a teacher or a content creator, you probably create a lot of content on a regular basis. If you're like me, you probably use Google Docs to write content and a spreadsheet to manage your content creation and editorial workflow.

In this tutorial series, I will share how I've automated various steps in my content creation workflow. Leveraging automation has made me more productive and this means I can focus more of my time on the actual content itself.

In this post I will walk you through how to create Google Docs from Google Sheets using Apps Script. Assuming you use a Google Sheets spreadsheet to manage your content calendar, you'll learn how to automatically create Google Docs for each topic you plan to write about and save links to each of these documents in your sheet. Once you write some Apps Script code to automate this process, whenever you need to create more documents, all you'll need to do is run your script and Google Docs will be automatically created for you. Let's go ahead and write this script.

Let's say you maintain your content calendar in Google Sheets. In this simple tutorial, we're going to use three columns:

• Title: A list of topics you plan to write about.

• Status: The status of each post.

• Doc: Links to Google Docs for each post.

What we want to do is use Apps Script to create Google Docs for each post and link to these documents from the Doc column. Here is how the above spreadsheet should look post running the script. You'll notice that there are now links to Google Docs in the 3rd column.

The script to automate creating Google Docs will work in the following way:

• Load the data in the spreadsheet.

• For each row in the sheet, check if the 3rd column is empty.

• If it is empty, it means a Google Doc does not exist yet. So create one using Apps Script and then add a link to the document in the 3rd column.

• Write the updated rows containing links to the newly created documents back to your Google Sheets spreadsheet.

I've added comments to the code below to help you understand what each step does.

function createDocsFromSheets() {
 // Load data from the spreadsheet
 var dataRange = SpreadsheetApp.getActive().getDataRange();
 var sheetContents = dataRange.getValues();

 // Save the header in a variable called header
 var header = sheetContents.shift();

 // Create an array to save the data to be written back to the sheet.
 var updatedContents = [];

 // Add the header to the array that will be written back to the sheet.
 updatedContents.push(header);

 // For each row, see if the 3rd column is empty.
 // If it is empty, it means that a doc hasn't been created yet.
 // Create a doc using the title from the first column
 sheetContents.forEach(function(row) {
   if(row[2] === "") {
     // row[0] contains the title of the post.
     // Create a Google Doc whose filename is the title of the post.
     var document = DocumentApp.create(row[0]);
     var documentId = document.getId();
     
     // Create the Google Doc's URL using the Id of the document.
     var documentUrl = `https://docs.google.com/document/d/${documentId}/edit`;

     // Add this URL to the 3rd column of the row and add this row
     // to the data to be written back to the sheet.
     row[2] = documentUrl;
     updatedContents.push(row);
   }
 });

 // Write the updated data back to the Google Sheets spreadsheet.
 dataRange.setValues(updatedContents);

}

If you'd like you can also create a custom menu to make it easy to run your script from the spreadsheet.

function onOpen() {
 SpreadsheetApp.getUi().createMenu("⚙️ Admin")
   .addItem("Create Docs", "createDocsFromSheets")
   .addToUi();
}

Here is a video demonstrating the script in action. As you can see below, we're able to create several documents with a single click. If you write lots of content, automating parts of your content creation process using Apps Script can make you a lot more productive.

Conclusion

In this tutorial I showed you how to create Google Documents from Google Sheets using Apps Script. Hope you found this tutorial helpful. Thanks for reading!