CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheet
user8702720
user8702720

Reputation: 115

How to use Query and Importrange using Apps Script

I need updated info from master sheets using query and importrange using apps script. This is master sheet. From which I need to access info: https://docs.google.com/spreadsheets/d/1tpv0iGwpnLUw6F5FoLn2jE4FBuF_igjriLA5uyNjczw/edit#gid=0

And this is sheet in which I need to show data: https://docs.google.com/spreadsheets/d/1ENZdtWI2f_wYzKbqUNziw6bVcTCpP6T4vkPbs-lPgC0/edit#gid=0

Currently I am using to fetch date. But I need to use Apps Script. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1tpv0iGwpnLUw6F5FoLn2jE4FBuF_igjriLA5uyNjczw/edit#gid=0","Task!A:J"),"SELECT* WHERE Col7 = 'JOHN'")

Upvotes: 1

Views: 3572

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

If I understand you correctly, you want to import data from a source spreadsheet to another spreadsheet when column G has a certain value.

In that case, you can use getValues() and setValues() for this:

const SOURCE_SPREADSHEET_ID = "1qkCxwyspeRrVcQRFqiHmIXRUjbrz9zgp09pYR0Y6_-M";
const TARGET_SPREADSHEET_ID = "1zp34NKnqRqdNvDzJx70T1spb0GbUQwnpUmJgkF81Pr4";
const SOURCE_SHEET_NAME = "Task";
const TARGET_SHEET_NAME = "Sheet1";
const NAME_COL_INDEX = 7;
const NAME = "JOHN";

function copyData() {
  const sourceSheet = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID).getSheetByName(SOURCE_SHEET_NAME);
  const sourceValues = sourceSheet.getRange("A1:J" + sourceSheet.getLastRow()).getValues();
  const targetValues = sourceValues.filter((row,i) => i === 0 || row[NAME_COL_INDEX-1] === NAME);
  const targetSheet = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID).getSheetByName(TARGET_SHEET_NAME);
  targetSheet.clearContents();
  targetSheet.getRange(1,1,targetValues.length,targetValues[0].length).setValues(targetValues);
}

function installOnEditTrigger() {
  const ss = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID);
  ScriptApp.newTrigger("copyData")
           .forSpreadsheet(ss)
           .onEdit()
           .create();
}

Note:

If you want this to update automatically whenever users edit the source spreadsheet, consider installing an onEdit trigger. To do that in the example above, execute installOnEditTrigger once.

Upvotes: 2

Related Questions