Update Google Doc Files With Data Pulled From a SpreadSheet Using Doc File Name-CodePudding

I need to assign a membership number to each request I receive from aspiring members of my association.

Aspiring members fill a Google Docs file. When their requests are validated, those files need to be updated with the assigned membership number.

Inside the spreadsheet I have two columns. One contains the membership number and the other contains the ID of the request.

Spreadsheet Screenshot

The doc files are named with the ID of the request as well. The membership numbers should be added in place of the placeholder {membershipnumber} found in the doc files.

Doc File Screenshot

Is anyone able to help?

Thanks a lot for your precious help!

I really have no idea how to accomplish this.

CodePudding user response：

I believe your goal is as follows.

You have Google Document files in the specific folder and have a Google Spreadsheet including the values for updating the Google Document files.
You want to replace the value of {membershipnumber} with the values of column "A" by searching the filename using the value of column "C".

In this case, how about the following sample script?

Sample script:

In this sample, please copy and paste the following script to the script editor of Google Spreadsheet and set the variables. And, save the script.

function myFunction() {
  const folderId = "###"; // Please set your folder ID of the folder including the Google Document files.
  const sheetName = "Sheet1"; // Please set your sheet name.

  const folder = DriveApp.getFolderById(folderId);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const obj = sheet.getRange("A2:C"   sheet.getLastRow()).getDisplayValues().reduce((o, [a, , c]) => (o[c] = a.trim(), o), {});
  const docs = folder.getFilesByType(MimeType.GOOGLE_DOCS);
  while (docs.hasNext()) {
    const file = docs.next();
    const filename = file.getName().trim();
    if (obj[filename]) {
      const doc = DocumentApp.openById(file.getId());
      doc.getBody().replaceText("{membershipnumber}", obj[filename]);
    }
  }
}

When this script is run, the Google Document files are updated using the values of column "A" by searching the Document files with the filename retrieved from column "C".

Sample script:

References: