Importing data dynamically in Google Sheets

Other Questions Google Sheets
1

I’m having trouble with a Google Sheets import script. It works but has some limitations. The script is tied to the number of rows in the source document. This means I can’t import until the row count matches. The source file changes often so I have to manually adjust the row count each time. Is there a way to import data regardless of row count? Also can I choose where to start the import like cell B2 instead of just A1? Here’s a simplified version of my current script:

function importData() {
  const sourceSheet = SpreadsheetApp.openById('sourceId').getSheetByName('sourceName');
  const targetSheet = SpreadsheetApp.openById('targetId').getSheetByName('targetName');
  
  const dataRange = sourceSheet.getRange('A:H');
  const data = dataRange.getValues();
  
  targetSheet.getRange('A:H').clearContent();
  targetSheet.getRange(dataRange.getA1Notation()).setValues(data);
}

Any help improving this would be great. Thanks!

2

I’ve encountered similar challenges with dynamic imports in Google Sheets. Here’s a solution that might work for you:

Use getDataRange() to capture all the data without specifying rows:

const sourceData = sourceSheet.getDataRange().getValues();

Then, to start at B2:

targetSheet.getRange(2, 2, sourceData.length, sourceData[0].length).setValues(sourceData);

This approach adapts to varying row counts and allows you to specify the starting cell. It is both flexible and requires less maintenance. One caveat is to ensure that your source data is clean to avoid importing empty rows—you might add a filter step if necessary.

Let me know if you implement this and how it works out for you.

3

yo dancingfox, i feel ur pain. try using getLastRow() to grab all the data without worrying bout row counts. like this:

const data = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), 8).getValues();

then for starting at B2, just change ur target range:

targetSheet.getRange(‘B2:I’ + (data.length + 1)).setValues(data);

hope this helps bro!

4

Hey there, I’ve dealt with similar import issues before. Here’s what worked for me:

Instead of specifying a fixed range, you can use getDataRange() to dynamically get all the data from the source sheet. This adapts to changing row counts automatically.

For choosing where to start the import, you can use offset() on the target range. Something like:

const sourceData = sourceSheet.getDataRange().getValues();
const targetRange = targetSheet.getRange('B2');
targetRange.offset(0, 0, sourceData.length, sourceData[0].length).setValues(sourceData);

This starts the paste at B2 and expands to fit all the source data. Hope that helps solve your problems! Let me know if you need any clarification on implementing these changes.