I believe your goal as follows.
- 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.
Modification points:
- 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.
Modified script 1:
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
});
}
}
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.
Before you use this script, please enable Sheets API and Drive API at Advanced Google services.
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);
});
}
References: