google-sheetsgoogle-apps-scriptgoogle-sheets-formula

Copy a formula to all the sheets in Google Spreedsheets


I have a Google Spreedsheets with a lot of Sheets (more than 400)...and I need to insert formulas (H2:H13) in all the Sheets.

I know that I can do it manually..taking Sheets one by one. I also tried this App Script, but this also means i need more than 400 App Scripts...

function Movedata1() {
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName("Sheet1");
  var dstSheet = ss.getSheetByName("Sheet2");
  var srcRange = srcSheet.getRange("H2:H13");
  var dstRange = dstSheet.getRange(dstSheet.getLastRow() - 11, 8);
  srcRange.copyTo(dstRange); // or srcRange.copyTo(dstRange, { contentsOnly: true });
}

I would need some help with App Script to make it works for all the sheets.

The link for of the Spreedsheets is here: https://docs.google.com/spreadsheets/d/1LR0i-2LCg6ZrfBGHpX-XVlScRgvcOxh1te9Wj_x7ObA/edit?gid=0#gid=0

THANKS!


Solution

  • Using getSheets() you can then loop thru all of the sheets, updating the formulas/values:

    function Movedata3() {
      const ss = SpreadsheetApp.getActive();
      const allsheets = ss.getSheets();
      const srcSheet = ss.getSheetByName("Sheet1");
      const srcRange = srcSheet.getRange("H2:H13");
      allsheets.forEach((sheet) => {
        const dstRange = sheet.getRange(sheet.getLastRow() - 11, 8);
        srcRange.copyTo(dstRange); // or srcRange.copyTo(dstRange, { contentsOnly: true });
      });
    }