how to apply a macro to a folder of google spreadsheets

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

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