CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
markbakerrr
markbakerrr

Reputation: 1

Automate dynamic IMPORTRANGE in google sheets

I am building a multi-section questionnaire (3 in total) and I want to have 4 sheets to hold the data (one master and one for each section).

How would I send data to another sheet from the master spreadsheet when a new row is added to the Master Sheet and make it dynamic so it does not pull the same row everytime?

I have found this script online:

script link

but it is for moving data between sheets unfortunately. Could it be remodeled?

Thanks!

Upvotes: 0

Views: 795

Answers (2)

alberto vielma
alberto vielma

Reputation: 2342

To send values to another sheet (besides from your main one), you can use the onFormSubmit function, it will be triggered every time the form is submitted and with its event object, which contains the information from your form, you will be able to pass those values into the other sheets as you want.

// This will be triggered every time the form is submitted
function onFormSubmit(e) {
  // Get all sheets in the active spreadsheet
  var sheetsArr = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  // Get the second sheet
  var slaveSheet1 = sheetsArr[1];
  // Get the row where the values will be inserted 
  var rowVals = slaveSheet1.getLastRow() + 1;
  // The number of cols where you will puth the values
  var numberOfCols = e.values.length;
  // Set values that came from the form
  slaveSheet1.getRange(rowVals, 1, 1, numberOfCols).setValues([e.values]);
}

Be careful with the installable triggers Restrictions.

Upvotes: 0

Ignacio Bescansa
Ignacio Bescansa

Reputation: 25

Perhaps the setFormula class can help you, it can be dynamic & you can update it with a trigger

sheet.getRange(1,1,1,1).setFormula('=IMPORTRANGE("SPREADSHEET_URL", "SHEET_NAME!'+rangevariable1inA1notation+':'+rangevariable2inA1notation+'")');

Make sure you get the range you want, and the notation of those 2 variables to get the dynamic range:

var rangevariable1inA1notation = sheet.getRange(1,1).getA1Notation(); // for example, instead of 1,1 it could be your own variable
var rangevariable2inA1notation = sheet.getRange(2,2).getA1Notation(); //for example

Upvotes: 1

Related Questions