Creating a Macro that Loops through All Sheets

0
votes

The following macros code creates a simple scatter-plot graph. I have 100 sheets in my Google Spreadsheet and I just want the code to loop through each sheet.

I know I need a for (var i=0, i<101 (sheets.lentgh, i++1)....

I'm just trying to figure out where to put the I that directs the code to loop through each sheet.




function _1vs28() {






 var spreadsheet = SpreadsheetApp.getActive();




  spreadsheet.getRange('G1').activate();



  var sheet = spreadsheet.getActiveSheet();

  var chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange('F1:I97'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  var charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('F1:I97'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('G1:G100000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('I1:I10000'))
  .addRange(spreadsheet.getRange('G1:G10000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', true)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('I1:I10000'))
  .addRange(spreadsheet.getRange('G1:G10000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', true)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  spreadsheet.getRange('H978').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
  };



function newsheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B17').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Glucocil'), true);
};

function newsheet1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B20').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Glucocil'), true);
  spreadsheet.getRange('C16').activate();
};```
1
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

1 Answers

0
votes

Loop Through All Your Sheets

function loopThroughAllSheets() {
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  //var html='<h1>All My Sheets</h1>';
  for(var i=0;i<shts.length;i++) {
    //html+=Utilities.formatString('Sheet Name: <strong>%s</strong><br />',shts[i].getName());
    doItOverAndOver(shts[i],ss);
  }
  //html+='<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
  //var userInterface=HtmlService.createHtmlOutput(html);
  //SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Sheets');
}

To answer your additional question what do you do with this? Well, if your happy with your current script then perhaps you can turn it into a function as shown below and call it as shown above;

function doitOverAndOver(sht,ss) {
  var spreadsheet = ss;
  spreadsheet.getRange('G1').activate();//I think you need a sheet specifier in here
  var sheet = sht;
  var chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange('F1:I97'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  var charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('F1:I97'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('G1:G100000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', false)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('I1:I10000'))
  .addRange(spreadsheet.getRange('G1:G10000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', true)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  charts = sheet.getCharts();
  chart = charts[charts.length - 1];
  sheet.removeChart(chart);
  chart = sheet.newChart()
  .asScatterChart()
  .addRange(spreadsheet.getRange('I1:I10000'))
  .addRange(spreadsheet.getRange('G1:G10000'))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setOption('useFirstColumnAsDomain', true)
  .setOption('isStacked', 'false')
  .setOption('title', 'Website Purchases, Website Purchases [1 Day After Clicking], Website Purchases [7 Days After Clicking] and Website Purchases [28 Days After Clicking]')
  .setPosition(7, 2, 350, 18)
  .build();
  sheet.insertChart(chart);
  spreadsheet.getRange('H978').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
};

Personally, I would want to clean up your function but that's another question.