There is a script that imports data from one Google table to another.

The problem is that both documents must be identical in the number of lines. Since the first document is dynamic, the second just has to be corrected by hand to the required number of lines (as in the first document).

Tell me how to get rid of this addiction? To import a range, regardless of how many lines are in the document that receives this data. And how to import not only in cell A1, but, for example, in cell B2? C3? and so on ... Sample data

function onOpen2(e) { // исходный документ var sss = SpreadsheetApp.openById('Ключ'); // исходный лист var ss = sss.getSheetByName('Лист'); // Получить диапазон данных var SRange = ss.getRange('A:D'); // Скопировать в ячейку. В данном случае (A1) var A1Range = SRange.getA1Notation(); // получить значения данных в диапазоне var SData = SRange.getValues(); // Целевая таблица. Куда выгружаем var tss = SpreadsheetApp.openById('Ключ'); // Целевой лист. Куда импортируем var ts = tss.getSheetByName('лист'); // Очищение диапазона перед копированием. ts.getRange('A1:D').clearContent(); // Скопировать в ячейку. В данном случае (A1) ts.getRange(A1Range).setValues(SData); }
  • It is impossible to understand. What are the lines? What is the dynamics? Why all this? Maybe start with an example? What is the essence of your title for the topic? - oshliaer
  • Added links to the example. This is necessary in order to be able to import data from one document to another. Formula importrange - can not cope, because it has limitations of 25 thousand rows (I have an array of 100k + 10+ columns), so I can only import this array using a Google script. But the problem is that two files must be the same size, if the source file has 100 thousand lines, then the second one should have 100 thousand lines. How to remove this dependency when importing data? - Nikita Telegin

1 answer 1

The code from the topic is a bit strange. With an abundance of comments, it is not clear why the copying problem arose. Here is a working example.

 /** * Copy values between two sheets * * @param {GoogleAppsScript.Spreadsheet.Sheet} from Source sheet * @param {GoogleAppsScript.Spreadsheet.Sheet} to Destination sheet * @returns {undefined} */ function copyValuesBetweenTwoSheets_(from, to) { var values = from.getRange('A2:D').getValues(); to.clearContents(); to.getRange(3, 3, values.length, values[0].length).setValues(values); }

The example shows that C3 is the cell from which the insertion begins, although the data is taken from cell A2 and beyond.

A working example is here .

  • one
    Thank you, kind man :) - Nikita Telegin
  • You are welcome. You can mark the answer as a solution. - oshliaer