Google表格数组clearcontent [英] Array for Google Sheet clearcontent

问题描述

我有很多Google表格分为特定的文件夹.我的文件夹有十张纸.每个工作表都有12个电子表格.他们都是一样的.我需要擦除所有相同范围的所有电子表格的特定单元格并将值设置为其他值，并且我只能使用12个电子表格中的数组代码来完成此操作.我需要的是为文件夹运行的数组代码，这是一种运行代码并一次完成120个电子表格的所有工作的方法.

I have many Google sheets divided into specific folders. I have 10 sheets by folder. Each sheet has 12 spreadsheets. All them are the same. I need to erase specific cells and set values into others for all spreadsheets, the same range, and I can do this with an array code only into sheet, for 12 the spreadsheet. What I need is an array code to run for folders, a way to run my code and do all the work for the 120 spreadsheets at once.

我试图增强此代码，但是它不起作用，当我运行此代码时，什么也没有发生，也没有错误消息.

I have tried to enhance this code, but it doesn't work, when I run this code, nothing happens, neither error message.

function apagarTUTMOD2(){
  var folder = DriveApp.getFolderById("folder ID");
  var p = folder.getFiles();
for (i in p){
var sheet = p[i].getSheets();
  for(j in sheet) {
  sheet[j].getRange('F5:F164').clearContent(); //clear this range
  sheet[j].getRange('F105:F164').setValue("X") // write "X" into this range
  }
}
}

我认为某事指的文件夹是错误的，但我不知道如何解决.感谢您的帮助.

I think something referring to folders is wrong, but I don't know how to fix it. Thanks for any help.

推荐答案

我相信您的目标如下.

• 您要从特定文件夹中检索Google Spreadsheets.
• 有120个Google电子表格，每个电子表格有12张纸.
• 您要清除范围"F5:F164"，在所有工作表中.
• 您要将 X 的值放在"F105:F164"范围内，在所有工作表中.
• 您想使用Google Apps脚本实现这一目标.

• You want to retrieve Google Spreadsheets from the specific folder.
• There are 120 Google Spreadsheets and each Spreadsheet has 12 sheets.
• You want to clear the range "F5:F164" in all sheets.
• You want to put the value of X to the range "F105:F164" in all sheets.
• You want to achieve this using Google Apps Script.

• 在脚本中， folder.getFiles()返回FileIterator.在这种情况下， next()方法用于检索文件对象.
"li for"的处理成本为:比forEach高一点.参考
• 为了将FileIterator中的文件对象与Spreadsheet服务一起使用，可以使用 SpreadsheetApp.open().

• In your script, folder.getFiles() returns FileIterator. In this case, next() method is used for retrieving the file object.
• The process cost of "for in" is a bit higher than forEach. Ref
• In order to use the file object from FileIterator with the Spreadsheet service, you can use SpreadsheetApp.open().

当以上几点反映到您的脚本中时，它如下所示.

When above points are reflected to your script, it becomes as follows.

function apagarTUTMOD2() {
  var folder = DriveApp.getFolderById("folder ID");
  var p = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
  while (p.hasNext()) {
    SpreadsheetApp.open(p.next()).getSheets().forEach(sheet => {
      sheet.getRange('F5:F164').clearContent(); //clear this range
      sheet.getRange('F105:F164').setValue("X") // write "X" into this range
    });
  }
}

修改后的脚本2:

从一种运行我的代码并一次完成120个电子表格的所有工作的方式.，我担心上述脚本一次运行是否可以直接适合您的实际情况.因此，在这个答案中，我还要提出以下脚本.以下脚本使用Sheets API和Drive API.使用这些API，可以降低处理成本.

Modified script 2:

From a way to run my code and do all the work for the 120 spreadsheets at once., I'm worry whether above script can directly work for your actual situation by one running. So in this answer, I would like to also propose the following script. The following script uses Sheets API and Drive API. When those APIs are used, the process cost will be able to be reduced.

在使用此脚本之前，请启用Sheets API和Drive API在高级Google服务中.

function apagarTUTMOD2_2() {
  const folderId = "folder ID";  // Please set the folder ID.

  const files = Drive.Files.list({maxResults: 1000, q: `'${folderId}' in parents and trashed=false and mimeType='${MimeType.GOOGLE_SHEETS}'`}).items;
  files.forEach(({id}) => {
    const sheetIds = Sheets.Spreadsheets.get(id, {fields: "sheets(properties(sheetId))"}).sheets;
    sheetIds[0].properties.sheetId
    const requests = sheetIds.flatMap(o => [
      {repeatCell:{range:{sheetId:o.properties.sheetId,startRowIndex:4,endRowIndex:164,startColumnIndex:5,endColumnIndex:6},cell:{userEnteredValue:{}},fields:"userEnteredValue"}},
      {repeatCell:{range:{sheetId:o.properties.sheetId,startRowIndex:104,endRowIndex:164,startColumnIndex:5,endColumnIndex:6},cell:{userEnteredValue:{stringValue:"X"}},fields:"userEnteredValue"}}
    ]);
    Sheets.Spreadsheets.batchUpdate({requests: requests}, id);
  });
}

参考文献:

• 类文件夹的getFiles()
• FileIterator
• Sheets API
• 驱动器API

这篇关于Google表格数组clearcontent的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！