M
M
maxrich144442022-02-03 09:46:23
Google Apps Script
maxrich14444, 2022-02-03 09:46:23

Google APPS - How to run multiple scripts?

There is a script that automatically populates the Google Doc using the values ​​in the Google Spreadsheet.
The script works only with one Google document, but I need it to fill in other documents
Or repeat the same script so that they run one after the other

The code for one document looks like this

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs')
  menu.addToUi();

}

function createNewGoogleDocs() {
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('1VohEX7CXezxp7I8SZ_Z2VaucR8t_jJ3iny1EwbOYTXg');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('1PldTQREZeQ0Ukvj7T-T0_m4TM8JoJ2bD')
  //Here we store the sheet as a variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getValues();
  
  //Start processing each spreadsheet row
  rows.forEach(function(row, index){
    //Here we check if this row is the headers, if so we skip it
    if (index === 0) return;
    //Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
    if (row[16]) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Agreement` , destinationFolder)
    //Once we have the copy, we then open it using the DocumentApp
    const doc = DocumentApp.openById(copy.getId())
    //All of the content lives in the body, so we get that for editing
    const body = doc.getBody();
    //In this line we do some friendly date formatting, that may or may not work for you locale
    const friendlyDate = new Date(row[12]).toLocaleDateString();
    
    //In these lines, we replace our replacement tokens with values from our spreadsheet row
    body.replaceText('{{Student name ENG}}', row[0]);
    body.replaceText('{{Student name RUS}}', row[1]);
    body.replaceText('{{Agreement Number}}', row[2]);
    body.replaceText('{{Passport}}', row[3]);
    body.replaceText('{{Issued ENG}}', row[4]);
    body.replaceText('{{Issued RUS}}', row[5]);
    body.replaceText('{{What Degree}}', row[6]);
    body.replaceText('{{What Degree RUS}}', row[7]);
    body.replaceText('{{Phone}}', row[8]);
    body.replaceText('{{E-mail}}', row[9]);
    body.replaceText('{{Citizenship ENG}}', row[10]);
    body.replaceText('{{Citizenship RUS}}', row[11]);
    body.replaceText('{{Datee}}', friendlyDate);
    body.replaceText('{{Duration}}', row[13]);
    body.replaceText('{{Duration RUS}}', row[14]);
    
    //We make our changes permanent by saving and closing the document
    doc.saveAndClose();
    //Store the url of our new document in a variable
    const url = doc.getUrl();
    //Write that value back to the 'Document Link' column in the spreadsheet. 
    sheet.getRange(index + 1, 16).setValue(url)
    
  })
  
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2022-03-10
@maxrich14444

You must rearrange the code to sheetpoint to the desired sheet, for example, instead of

const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Data')

const id = 'ABSDADFASDFSDFASDFASD123123';
  const sheet = SpreadsheetApp
    .getSpreadsheetById(id)
    .getSheetByName('Data')

Next, break the function into parts that can call this code, for example,
const id = 'ABSDADFASDFSDFASDFASD123123';
  createNewGoogleDocs(id);

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question