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.
