I want to sum the values present in different sheets(located in different spreadsheets) in a new google sheet using google app script

Question

I want to add values present in different sheets(located in different spreadsheets) in a new google sheet using google app script. I am new to this and have no idea how to proceed. I would appreciate if someone could help me with the same.

Answer 1

You will need to add your own spreadsheet id's and sheetnames to the Data Sheet and then all the data in those sheets will be displayed on the Comparisons Sheet.

function getData() {
  var ss=SpreadsheetApp.getActive();
  var ish=ss.getSheetByName('Data');
  var iA=ish.getDataRange().getValues();
  var dsh=ss.getSheetByName('Comparisons');
  dsh.clearContents();
  dsh.getRange('A1').setValue('Displaying Data from selected spreadsheets list in Data Sheet.').setFontWeight("Bold");
  for(var i=1;i<iA.length;i++) {
    if(iA[i][0] && iA[i][1]) {
      var css=SpreadsheetApp.openById(iA[i][0]);
      var csh=css.getSheetByName(iA[i][1]);
      var vA=csh.getDataRange().getValues();
      var desc=Utilities.formatString('Data from %s',iA[i][1]);
      dsh.getRange(dsh.getLastRow()+1,1).setValue(desc).setFontWeight("Bold");
      dsh.getRange(dsh.getLastRow()+1,1,vA.length,vA[0].length).setValues(vA);
    }
  }
}

This is the format of my Data Sheet:

This is what my Comparisons Sheet looks like after running it with two other spreadsheets listed:

Adding Data from other Spreadsheets

function getData() {
  var ss=SpreadsheetApp.getActive();
  var ish=ss.getSheetByName('Data');
  var iA=ish.getDataRange().getValues();
  var dsh=ss.getSheetByName('Sum');
  dsh.clearContents();
  for(var i=1;i<iA.length;i++) {
    if(iA[i][0] && iA[i][1]) {
      var css=SpreadsheetApp.openById(iA[i][0]);
      var csh=css.getSheetByName(iA[i][1]);
      dsh.appendRow([csh.getRange(iA[i][2]).getValue()]);
    }
  }
  var rg=dsh.getRange(dsh.getLastRow()+1,1).setFormula("=Sum(" +  dsh.getRange(1,1,i-1,1).getA1Notation() + ")");
  rg.offset(0,1).setValue("Total").setFontWeight("Bold")
}

Data Sheet:

Sum Sheet: