CODEWITHSUNDEEP
google-apps-script
Fowzee
Fowzee

Reputation: 21

Searching current folder for spreadsheets and changing value in specific cell of each file found

New to Google Apps script here, but have some coding experience. I want to scan current folder for spreadsheets. For each spreadsheet found, I want to change the value in a specific cell (say cell F16 in "Sheet1") to "Q1 FY16". Here is what I have so far:

function myFunction() {

var folderID ="0BxfGszImm3D9flpVlWXd4bjQ";
var topFolder = DriveApp.getFolderById(folderID);
Logger.log(topFolder.getName());
var filesList = topFolder.getFiles();

while (filesList.hasNext()) {
    var file = filesList.next();
    Logger.log(file.getName());
    file.getSheetByName("Sheet1").getRange("F16").setValue("Q1 FY16");
 }
}

There are two main problems:

  1. I have to specify a folder ID in this and I don't want to. I want the code to run in the current directory (and eventually I will make it recursive to scan all subfolders as well).
  2. The File class doesn't have the "getSheetByName()" or "getRange()" methods, but I don't know how to cast the files into Spreadsheets.

Any help with this would be greatly appreciated.

Cheers

Upvotes: 1

Views: 412

Answers (1)

OrangeBender
OrangeBender

Reputation: 76

  1. Where will you be launching this script from? They are no way of launching script directly from google drive.

  2. to Be able to use the getSheetByName() and the getRange() you need to open the file as a spreadsheet. instead of using this line:

    file.getSheetByName("Sheet1").getRange("F16").setValue("Q1 FY16");

You should use something like this :

try {
SpreadsheetApp.openById(file.getId()).getSheetByName("Sheet1").getRange("F16").setValue("Q1 FY16");
} 
catch(e){}

you need to use the try - catch since some of the files won't be spreadsheet and give and error when trying to use the SpreadsheetApp.openById().

I hope this helps you a bit, I'll try to update this answer once I get more information for the first part.

Best of luck.

Upvotes: 0

Related Questions