How can I do ImportRange with Google Apps Script?

Question

I would like to do something similar in a Google Apps Script.

=TRANSPOSE(index(importrange('Feuilles de référence'!$C$9;"'PLANNING PROD'!$A$1:$i$100");
           EQUIV(SERIE.JOUR.OUVRE.INTL(A1;1;7);
                 importrange('Feuilles de référence'!$C$9;"'PLANNING PROD'!$A$1:$A$100");0)))

Do you know what can I write?

I want to import a range of cells from a sheet on another spreadsheet (not a sheet on the spreadsheet where the script will reside).

Answer 1

I would revise Ruben's broad steps as:

1. Get the destination spreadsheet.
2. Get the destination cell from the correct sheet.
3. SetValue with the importrange formula.
4. Go to destination sheet and approve access to origin spreadsheet on destination cell

Here is my code:

function ExportRange() {
  var destination = SpreadsheetApp.openById(DESTINATION SPREADSHEET ID HERE);
  var destinationSheet = destination.getActiveSheet();      
  var destinationCell = destinationSheet.getRange(SET DESTINATION CELL EXAMPLE:"A1");
  var cellData = '=IMPORTRANGE(origin spreadsheet_key, range_string)';
  destinationCell.setValue(cellData);
}

Answer 2

The broad steps that your code could do are the following:

1. Open the origin spreadsheet.
2. Get the data to be imported.
3. Do the required data operations*.
4. Add the result to the destination spreadsheet.

Most of the above steps could be done by using the Spreadsheet Service (SpreadsheeApp) but not all.

* Your code could use pure JavaScript, Google Apps Script Services, Google Apps Script libraries o web services that your code were able to call by using UrlFetchApp but not built-in spreadsheet functions as it's not possible to evaluate them on Google Apps Script.