Google Apps Script: Merge Google Sheets to Google Docs

Job ID: 32153503

Budget: $100 – $300 USD

Create a Google Apps Script to merge fields from Google Sheets to a new Google Docs file.

Requirements:
- Google App Script within the Google Sheet
- Copies an existing Google Docs template, creates a new duplicate Google Doc file to a different existing Drive folder
- Update the file name of the new Google Doc
- Takes Named Range fields from Google Sheets tab named "SOW Variables" (not cell references, must be Named Ranges)
- Replaces {{Tags}} within the newly copied Google Doc with Named Ranges from Google Sheets
-- For example: replace {{Zip Code}} within the newly copied Google Doc with the named range ZipCode within Google Sheet
-- The Google Doc template stays as-is and is not modified
- Opens the newly created Google Doc for the user
- Add the script as a Menu Item in the Google Sheet
- When the script runs, prompt user with a Yes (proceed with script) or No (cancel script) question
- Ensure proper/the same formatting from and in the Google Doc (line spacing, and bold or italics or normal)
- In the future, the Google Sheet will be copied/duplicated, and the script should still work even if it's run from a different Google Sheet
- The sync is one way (Sheets to Docs) and one time per script run (not in real-time/not continuously updating)


The Google Sheet:
https://docs.google.com/spreadsheets/d/1CSEx0qBLoIr1CSGHGGAk6_Vkm5UBLe5_mtG33EtA8ms/edit?usp=sharing


The Google Doc Template:
https://docs.google.com/document/d/1hIL47wfGE0cFVt1UqJWEMlm5DaX3l7J6TgVczfOIcnw/edit?usp=sharing


The Google Drive Folder For Generated SOWs:
https://drive.google.com/drive/folders/14X2TzR_DxM5q_1nmdAj_0GHvS63jqarg?usp=sharing


I started to write the script, but got stumped:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Generate SOW');

menu.addItem('Create New SOW', 'createNewSOW');
menu.addToUi();
}

function createNewSOW() {


// before script runs, show a prompt to user "Did you check for accuracy?" Yes (proceeds) or No (goes back, closes script)


const SOWTemplate = DriveApp.getFileById('1hIL47wfGE0cFVt1UqJWEMlm5DaX3l7J6TgVczfOIcnw');
const destinationFolder = DriveApp.getFolderById('14X2TzR_DxM5q_1nmdAj_0GHvS63jqarg');
const JEFE = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SOW Variables');

// const City = JEFE.NEED TO GET USING NAMED RANGES;

const newSOW = SOWTemplate.makeCopy('NEWLY CREATED SOW NEEDS TITLE FORMULA HERE Should be SOW Template - SCRIPT TESTING - {{(Names}}))', destinationFolder);
const newSOWGoogleDoc = DocumentApp.openById(newSOW.getId());
const body = newSOWGoogleDoc.getBody();

body.replaceText('{{City}}', city);

}