Google App Script + Google Sheet - Loop over each cell in active sheet and add formula with local cell address

Question

I had a search around and couldn't quite find/break apart the information I need to make this work. Apologies if there is indeed a valid question for this already.

I need to:

Fetch the active spreadsheet
Select every cell in the spreadsheet
Inject a formula into every cell but crucially it must add the local cell address to the end of the formula I inject. EG: if I inject the formula "=IMPORTRANGE("my.url.com", "My Sheet!!A1") then the next cell should be A2 and the formula should end in A2, repeat from A1 to AA69.

For a fuller understanding

I am linking two separate Google Sheets together. I use IMPORTRANGE to read in the URL of the target sheet, then I grab the desired 'Sheet Tab' from the target sheet.
Then I have a template duplicate of the target sheet in which I would like to replicate all the target information. Eg: MySheet is a blank duplicate of TargetSheet, I need to replicate all the info from TargetSheet to MySheet using IMPORTRANGE, however there are many teams that use the same layout sheet but all have unique URLs and I am building a sheet generator, so the script speeds this up massively.

I have the standard sheet grab that works fine:

var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var rangeList = activeSheet.getRange(['A1:AA69']);

And i have built my formula from other variables, which comes together easily enough:

// Create a combined formula from above variables, at present the only part missing is the cell to inject this into and closing parenthesis.
var IMPORTRANGE_FORMULACONSTRUCT = IMPORTRANGE_BEGINFORMULA_VALUE + "(" + '"' + SPREADSHEETURLVALUE + '"' + ", " + '"' + IMPORTRANGE_INFORMATIONTAB_VALUE + "!" ;
// Print to Logs (View with Ctrl + Enter)
Logger.log(IMPORTRANGE_FORMULACONSTRUCT);

How can I loop over every cell and add each cell to the end of my formula?

Help appreciated.

ziganotschka ziganotschka · Accepted Answer · 2020-01-22T13:33:40

You can retrieve the A1 notation of each cell in your range and use it for the local address in your formula.

Sample:

function myFunction() {
  var IMPORTRANGE_BEGINFORMULA_VALUE = "=IMPORTRANGE";
  var SPREADSHEETURLVALUE = "my.url.com"
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var IMPORTRANGE_INFORMATIONTAB_VALUE = activeSheet.getName();
  var rangeList = activeSheet.getRange('A1:AA69');
  var array=[];
  for( var i = 0; i < rangeList.getLastRow(); i++){
    array[i]=[];
    for( var j = 0; j < rangeList.getLastColumn(); j++){    
      var notation = activeSheet.getRange(i+1,j+1).getA1Notation();
      var notation = rangeList.getCell(i+1,j+1).getA1Notation();
      // Create a combined formula from above variables, at present the only part missing is the cell to inject this into and closing parenthesis.
      var IMPORTRANGE_FORMULACONSTRUCT = IMPORTRANGE_BEGINFORMULA_VALUE + "(" + '"' + SPREADSHEETURLVALUE + '"' + ", " + '"' + IMPORTRANGE_INFORMATIONTAB_VALUE + "!" + notation + '")' ;
      // Print to Logs (View with Ctrl + Enter)
      array[i].push(IMPORTRANGE_FORMULACONSTRUCT);
    }
  }
  rangeList.setValues(array);
}

Keep in mind that this code might be slow if you iterate over a large range.

Google App Script + Google Sheet - Loop over each cell in active sheet and add formula with local cell address

1 Answers