While there a few ways to iterate over a spreadsheet, here using sample code:

function onOpen(e) {

  menuItems();
}

function menuItems() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Custom Menu')
    .addItem('iterate', 'iterateThroughRows')
    .addToUi();
}

function iterateThroughRows() {
  var data = SpreadsheetApp.getActive().getSheetByName("data");
  var front = SpreadsheetApp.getActive().getSheetByName("front sheet");
  var values = data.getDataRange().getValues();
  values.forEach(function (row) {
    Logger.log(row);
    SpreadsheetApp.getUi().alert(row);
    //front.getRange().setValues();  //setRange? delete all data first?
  });
}

So far as it goes, this works in that an alert pops up showing the data. Looking to overwrite the entire front page sheet with all data from the data sheet. How is it specified to write the values to the front sheet?

For the sake of simplicity, only concerned with data and not formulas or formatting.

CodePudding user response：

To replace the contents of the 'front' sheet with the values in the 'data' sheet, use Sheet.clearContents(), Sheet.getDataRange(), Range.offset(), Range.getValues() and Range.setValues(), like this:

function replaceFrontSheetContentsWithDataSheetValues() {
  const ss = SpreadsheetApp.getActive();
  const frontSheet = ss.getSheetByName('front sheet');
  const dataSheet = ss.getSheetByName('data');
  const values = dataSheet.getDataRange().getValues();
  frontSheet
    .clearContents()
    .getDataRange()
    .offset(0, 0, values.length, values[0].length)
    .setValues(values);
}

CodePudding user response：

Copy all of the sheets of a spreadsheet to the first sheet

function copysstosh() {
  const ss = SpreadsheetApp.getActive();
  const tsh = ss.getSheets()[0];
  tsh.clearContents();
  tsh.appendRow([`Spreadsheet Copy: ${new Date()}`]);
  ss.getSheets().filter((s,i) => i != 0).forEach(sh => {
    let vs = sh.getDataRange().getValues();
    tsh.getRange(tsh.getLastRow()   1,1,vs.length,vs[0].length).setValues(vs);
  });
}