If my understanding is correct, you want to accomplish the following:
- Import data from one spreadsheet to another using
IMPORTRANGE.
- Add
notes manually to a column in your destination spreadsheet.
- When a new row is imported to the destination spreadsheet and make previously imported data, the notes should move too.
To achieve that, you would need to keep track of which note belongs to which row of imported data. Both sets of data should be somehow attached. Considering that you have a timestamp in column A, and that this timestamp is probably unique for each row, this timestamp could be used to attach both (if that's not possible, I'd propose adding another column that will be used to identify each row without ambiguity, via some kind of id).
At this point, I would consider using Google Apps Script to accomplish your needs. With this tool, you could develop the functionality that =QUERY(IMPORTRANGE(...)) is providing right now, and you could use other Apps Script tools to reach the desired outcome. Two tools could be specially necessary to accomplish this:
- onEdit triggers, to keep track of when the different spreadsheets are edited and make the appropriate changes if that's the case (basically, copying data from one spreadsheet to another).
- Properties Service, to store the information about which
note is attached to which row of data.
You could do something on the following lines:
Install two edit triggers, (1) one that will fire a function when the source spreadsheet is edited, and (2) another one that will fire when the destination spreadsheet is edited (a simple trigger cannot be used because you have to reference files to which your spreadsheet might not be bound). You can do this manually or programmatically.
Create a function that, for each note that is added to the destination sheet (in this code sample, that's in column D, please change according to your preferences), stores a key-value pair where the key is the value in column A (which should uniquely identify a row of data) and value is the note. This will be used later for the script to know where each note belongs to:
function storeNotes(e) {
var scriptProperties = PropertiesService.getScriptProperties();
var cell = e.range;
var sheet = cell.getSheet();
var rowIndex = cell.getRow();
var column = cell.getColumn();
var noteColumn = 4; // The column where notes are written, change accordingly
// Check whether correct sheet, column and row is edited:
if (column == noteColumn && rowIndex > 1 && sheet.getName() == "Destination") {
var row = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];
scriptProperties.setProperty(row[0], row[noteColumn - 1]); // Store property to script properties
}
}
- Create a function that, every time the source spreadsheet is edited, will delete all content in the destination spreadsheet and copy the data from the source. Then, it will look at the script properties that were store and, using this information, it will write the notes to the appropriate rows (because I see you only want to copy/paste some of the columns, in this sample some of the columns - the ones whose index is in
columnsToDelete - are not copied/pasted, you can change this easily to your preferences):
function copyData(e) {
var range = e.range;
var origin = range.getSheet();
var row = range.getRow();
if (origin.getName() == "Origin" && row > 1) { // Check if edited sheet is called "Origin" and edited row is not a header.
var dest = SpreadsheetApp.openById("your-destination-spreadsheet-id").getSheetByName("Destination");
var firstRow = 2;
var firstCol = 1;
var numRows = origin.getLastRow() - 1;
var numCols = origin.getLastColumn();
var values = origin.getRange(firstRow, firstCol, numRows, numCols).getValues();
// Removing some of the columns to get copied/pasted (in this case B and D):
var columnsToDelete = [1, 3];
values = values.map(function(row) {
for (var i = row.length; i > 0; i--) {
for (var j = 0; j < columnsToDelete.length; j++) {
if (i == columnsToDelete[j]) {
row.splice(i, 1);
}
}
}
return row;
})
// Copying content from source to destination:
var firstRowDest = 2;
var firstColDest = 1;
var numRowsDest = values.length;
var numColsDest = values[0].length;
var noteColumn = 4;
var currentValues = dest.getDataRange().getValues();
if (currentValues.length > 1) dest.deleteRows(2, dest.getLastRow() - 1);
var importedRange = dest.getRange(firstRowDest, firstColDest, numRowsDest, numColsDest);
importedRange.setValues(values);
// Writing notes stored in Properties in the appropriate rows:
var properties = PropertiesService.getScriptProperties().getProperties();
for (var i = 0; i < values.length; i++) {
for (var key in properties) {
if (key == values[i][0]) {
dest.getRange(i + 2, noteColumn).setValue(properties[key])
}
}
}
}
}
Notes:
- All these functions should be in the same script if you want all both functions to use Properties.
- In this sample, the sheet with source data is called
Origin and the sheet where it is copied is called Destination (from what I understood, they are in different spreadsheets).
- In this simplified example, columns A, B, E from source sheet get copied to columns A, B, C of the destination sheet, and notes are added to column D. Please change this to fit your case by modifying the corresponding indexes.
I hope this is of any help.
