1
votes

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.

1
It's always good to be more specific than "it didn't work". What happened - was there an error message (if so, what did it say)? Did it do something else unexpected (if so, what)? Did smoke start coming out of your computer (if so, turn it off and call firefighters if necessary!)?psmears
Ok. I have changed the text. But when I run this code, nothing happens, neither error message.Ivan Sinigaglia

1 Answers

2
votes

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: