How to run a script that will set values on different sheet

Question

I share this short piece of my code. I have a trouble. When I run this script in google spreadsheet, It works only in 1 sheet ("VT.Attivita") and not also in the second one ("Pratica(REF)"). I want my formula can do something in a sheet and then do something else in another one sheet. I used "var sheet = ss.getSheetByName" but It doesn't seem to work...

//----------------------------------------------------------------------- COLONNA BJ -------------------------------------------------------------------(formula su tutte le celle delle ultime 20 righe della colonna)--
// formula su tutte le celle delle ultime 20 righe della colonna "% Pratica"
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("VT.Attivita");
 var firstRow = sheet.getLastRow()-19
 var cell = sheet.getRange(firstRow, 62, 20);
 cell.setFormula('=IF(BI' + firstRow + '="";"";AVERAGE(FILTER(BI:BI;J:J=J' + firstRow + ';NOT(BH:BH="");A:A="RU")))');     
//----------------------------------------------------------------------- COLONNE A:BJ ---------------------------------------(incolla solo valori di formule sulle celle di tutte le ultime 20 righe di queste colonne)--
//incolla solo valori (TUTTO)
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("VT.Attivita");
 var destination = ss.getSheetByName("VT.Attivita"); 
 var firstRow = sheet.getLastRow()-19
 var range = source.getRange('A' + firstRow +':BJ'+sheet.getLastRow());
 range.copyValuesToRange(destination, 1, 62, firstRow, sheet.getLastRow());       

//--------------------------------------------------------------------------------- EseguiFormulePraticaREF ---------------------------------------------------------------------------------
// cancella tutto
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Pratica(REF)"); 
var lastRow = sheet.getLastRow();
var range = sheet.getRange('A2:A' + lastRow);
range.clear({contentsOnly: true});
//formula su cella A2 FILTER
 var A2 = sheet.getRange(2, 1);
 A2.setFormula('=sort(unique(FILTER(VT.Attivita!J:J;ROW(VT.Attivita!J:J)>1)))');
// incolla solo valori
 var source = ss.getSheetByName("Pratica(REF)");
 var destination = ss.getSheetByName("Pratica(REF)");  
 var range = source.getRange('A1:A'+sheet.getLastRow());
 range.copyValuesToRange(destination, 1, 1, 1, sheet.getLastRow());

You can make a range like OtherSheetName!A1:Z10 or var OtherSheetRange=SpreadsheetApp.getActive().getSheetByName('Other Sheet').getRange("A1:C5"); and then treat just like any other range. — Cooper

Cooper Cooper · Accepted Answer · 2017-08-20T19:53:08

Here's a simple script that will change a value in all sheets in a spreadsheet unless A1 already has a value.

function myFunction() {
  var ss=SpreadsheetApp.getActive();
  var allSheets=ss.getSheets();
  for(var i=0;i<allSheets.length;i++)
  {
    if(!allSheets[i].getRange(1,1).getValue())
    {
      allSheets[i].getRange(1,1).setValue(i+1);
    }
  }
}

How to run a script that will set values on different sheet

1 Answers