如何使用Google APP脚本在多个文档中替换文本

huangapple go评论65阅读模式
英文:

How To Replace Text in Multiple Document With Google APP Script

问题

我有多个文档作为主文档。如果我想重复使用这些多个文件,我必须逐个编辑,因为每个文档都有一些要替换的文本。
我尝试使用Google APP脚本在单个文档中替换多个文本,并成功了。
如何在Google电子表格列表中使用文档ID替换多个文档中的多个文本?
以下是我尝试构建的脚本:

function replaceText() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheetIDList = spreadsheet.getSheetByName('RENAME');
 const colId = 4;
 const firstRowID = 2;
 const lastRowId = 17;
 const n = lastRowId - firstRowID;
 let startRow = firstRowID     // Baris awal data DOC ID

 var values = sheetIDList.getRange(startRow, colId, n).getDisplayValues();

 values.forEach(([docId]) => {
    if (!docId) return;
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();

 body.replaceText("Kepala SMK Negeri 7 Semarang", "Kepala SMK Negeri 3 Jepara");
 body.replaceText("SMK Negeri 7 Semarang", "SMK Negeri 3 Jepara");
 body.replaceText("Haris Wahyudi, S.Pd., M.Pd.", "SUSWANTO DJONY PURNAWAN, S.Pd., M.Pd");
 body.replaceText("19751222 200003 1 002", "19700531 200401 1 001");
 body.replaceText("Pembina Tk. I", "Pembina");
 body.replaceText("SEKOLAH MENENGAH KEJURUAN NEGERI 7 SEMARANG", "SEKOLAH MENENGAH KEJURUAN NEGERI 3 JEPARA");
          
  });
}
英文:

I have multiple document as Master Document. If I want to re-use this multiple files, I must edit it one by one, with every document has some text to replace.
I try to replace multiple Text at single document with Google APP Script, and work.
How to replace multiple text in multiple document with Document ID in the Google Spreadsheet List?
There is the script I Try to built.

 function replaceText() {
 const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 const sheetIDList = spreadsheet.getSheetByName('RENAME');
 const colId = 4;
 const firstRowID = 2;
 const lastRowId = 17;
 const n = lastRowId - firstRowID;
 let startRow = firstRowID     // Baris awal data DOC ID

 var values = sheetIDList.getRange(startRow, colId, n).getDisplayValues();

 values.forEach(([docId]) => {
    if (!docId) return;
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();

 body.replaceText("Kepala SMK Negeri 7 Semarang", "Kepala SMK Negeri 3 Jepara");
 body.replaceText("SMK Negeri 7 Semarang", "SMK Negeri 3 Jepara");
 body.replaceText("Haris Wahyudi, S.Pd., M.Pd.", "SUSWANTO DJONY PURNAWAN, S.Pd., M.Pd");
 body.replaceText("19751222 200003 1 002", "19700531 200401 1 001");
 body.replaceText("Pembina Tk. I", "Pembina");
 body.replaceText("SEKOLAH MENENGAH KEJURUAN NEGERI 7 SEMARANG", "SEKOLAH MENENGAH KEJURUAN NEGERI 3 JEPARA");
          
  });
 }

答案1

得分: 1

修改点:

  • 在您的脚本中,在for (let i = 0 ; i < n; i++)的下面正好使用了for (let i = 0 ; i < n; i++),这会导致循环无法正确运行。请注意这一点。
  • 在循环中使用了getValue。在这种情况下,处理成本会变得很高。

当这些点反映在您的脚本中时,以下修改如何?

修改后的脚本:

function replaceText() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetIDList = spreadsheet.getSheetByName('DOC ID');
  const colId = 3;
  const firstRowID = 2;
  const lastRowId = 17;
  const n = lastRowId - firstRowID;
  let startRow = firstRowID     // 数据DOC ID的起始行

  // 我修改了下面的脚本。
  var values = sheetIDList.getRange(startRow, colId, n).getDisplayValues();
  values.forEach(([docId]) => {
    if (!docId) return;
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    body.replaceText("Kepala SMK Negeri 7 Semarang", "Kepala SMA Negeri 13 Semarang");
    body.replaceText("SMK Negeri 7 Semarang", "SMA Negeri 13 Semarang");
    body.replaceText("Haris Wahyudi, S.Pd. M.Pd", "Rusmiyanto, S.Pd., M.Pd.");
    body.replaceText("19751222 200003 1 002", "19690812 199803 1 013");
    body.replaceText("Pembina Tk. I", "Pembina");
  });
}
  • 运行此脚本时,从“C2:C16”中检索文档ID,并对每个文档运行您的replaceText

注意:

  • 在这种情况下,如果文档ID无效,会出现错误。请注意这一点。

参考资料:

英文:

Modification points:

  • In your script, for (let i = 0 ; i < n; i++) is used just under the line of for (let i = 0 ; i < n; i++). By this, the loop doesn't correctly work. Please be careful about this.
  • getValue is used in a loop. In this case, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

function replaceText() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheetIDList = spreadsheet.getSheetByName('DOC ID');
  const colId = 3;
  const firstRowID = 2;
  const lastRowId = 17;
  const n = lastRowId - firstRowID;
  let startRow = firstRowID     // Baris awal data DOC ID

  // I modified the below script.
  var values = sheetIDList.getRange(startRow, colId, n).getDisplayValues();
  values.forEach(([docId]) => {
    if (!docId) return;
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    body.replaceText("Kepala SMK Negeri 7 Semarang", "Kepala SMA Negeri 13 Semarang");
    body.replaceText("SMK Negeri 7 Semarang", "SMA Negeri 13 Semarang");
    body.replaceText("Haris Wahyudi, S.Pd. M.Pd", "Rusmiyanto, S.Pd., M.Pd.");
    body.replaceText("19751222 200003 1 002", "19690812 199803 1 013");
    body.replaceText("Pembina Tk. I", "Pembina");
  });
}
  • When this script is run, the document IDs are retrieved from "C2:C16". And, your replaceText is run to each document.

Note:

  • In this case, when the document ID is invalid ID, an error occurs. Please be careful about this.

References:

huangapple
  • 本文由 发表于 2023年5月11日 08:18:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76223353.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定