I have a code that copies the data from one sheet and pastes it in another sheet in another spreadsheet. However, I want to paste the data in multiple spreadsheets that I have saved in multiple folders (with childfolders) and in those spreadsheets the sheet that I want to paste already exists but it is outdated. I would like to retrieve the google sheets spreadsheets from every child folder and paste the updated data in the same sheet in all of them. This is the code that I have:

function copy_end(){

var carpetas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('carpetas');  
var pegar = SpreadsheetApp.OpenbyId("ID").getSheetByName('carpetas'); //here I would like to paste the data to the sheet carpeta in multiple spreadsheets 
  var firstrow= 0;
  var lastrow =carpetas.getLastRow();
  var rows= lastrow-firstrow;
  var Range = carpetas.getRange(1,1,rows,4);
  var DataCopied = Range.getValues();

  // I defined the variables for better understanding      
  var startRow = pegar.getLastRow() 1; //  1 because remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].
  var startColumn = 1;
  var numRows = DataCopied.length;
  var numColumns = DataCopied[0].length;
  pegar.getRange(startRow, startColumn, numRows, numColumns).setValues(DataCopied);
}}

Does anybody know how I can add the multiple folders to the script? Any help is appreciated. Thank you in advance :)

CodePudding user response：

Answer:

Assuming that there are two levels of folders (the parent folders and their immediate children), I'd suggest the following workflow:

• Get an array of parent folder ids (folderIds in the sample below).
• For each folder id, get the corresponding Folder and get its subfolders via Folder.getFolders. Get all those subfolders as well as their parent folder in an array.
• Iterate through the array of the subfolders and their parent folder, and look for spreadsheets located in any of these, using Folder.getFilesByType.
• For each resulting spreadsheet, try to get its carpetas sheet. If it exists, copy your desired values (this is the part of the script you already had).

Code sample:

function copy_end(){
  const folderIds = ["FOLDER_ID_1", "FOLDER_ID_2"]; // Add your parent folder ids
  const carpetas = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('carpetas'); 
  const firstrow = 0;
  const lastrow = carpetas.getLastRow();
  const rows = lastrow-firstrow;
  const Range = carpetas.getRange(1,1,rows,4);
  const DataCopied = Range.getValues();  
  const startColumn = 1;
  const numRows = DataCopied.length;
  const numColumns = DataCopied[0].length;
  folderIds.forEach(folderId => {
    const parentFolder = DriveApp.getFolderById(folderId);
    const subfoldersIter = folder.getFolders();
    const folders = [parentFolder];
    while (subfoldersIter.hasNext()) {
      folders.push(folders.next());
    }
    folders.forEach(folder => {
      const filesIter = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
      while (filesIter.hasNext()) {
        const file = fileIter.next();
        const fileId = file.getId();
        const pegar = SpreadsheetApp.openById(fileId).getSheetByName('carpetas');
        if (pegar) {
          const startRow = pegar.getLastRow() 1;
          pegar.getRange(startRow, startColumn, numRows, numColumns).setValues(DataCopied);
        }
      }
    });
  });
}