how to apply a macro to a folder of google spreadsheets

Question

I'm new to JavaScript though have experience in Visual Basic. Really what I need is to write a macro that I can put in a folder full of google spreadsheets and writes "N/A" in specific cells. I understand I would want to use a standalone script.

I have the fill with "N/A" portion written, but don't know how to write the "apply to all files in this folder" portion. any hints?

Answer 1

You need to traverse the selected drive (using DriveApp) and fetch all spreadsheet files. Then traverse all the sheets of each spreadsheet file and replace those values.

function doTest() {
  var driveId = <driveId>;
  var drive = DriveApp.getFolderById(driveId);
  // fetch spreadsheets files on your drive folder
  var spreadsheets = drive.getFilesByType(MimeType.GOOGLE_SHEETS);

  // traverse all the spreadsheets
  while (spreadsheets.hasNext()) {
    var spreadsheetFile = spreadsheets.next();
    var spreadsheet = SpreadsheetApp.openById(spreadsheetFile.getId());
    // traverse all sheets per spreadsheet
    spreadsheet.getSheets().forEach(function (sheet) {
      // each sheet, replace B16:B19 values with 'N/A'
      sheet.getRange('B16:B19').setValue('N/A');
    });
  }
}

Reference:

• DriveApp
• SpreadsheetApp