How to import data from another Google Sheet using Google Script without using Spreadsheet ID

Question

I have a Google Script that imports my data range from one Google Sheet to another Google Sheet and it works perfectly.

My issue is however the original data sheet gets updated daily by a third party (CloudHQ) which replaces the original version, this means that a new spreadsheet ID is created which renders this script incorrect daily.

Is there a way to import a data range by another google script method by using something constant such as the file name or file location?

Please find my code below:

    var sourceSpreadsheetID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    var sourceWorksheetName = "CustomSheetName1";
    var targetSpreadsheetID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    var targetWorksheetName = "Sheet1";

function importData1() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  //var thisData = thisWorksheet.getDataRange();
  var thisData = thisSpreadsheet.getRangeByName("B:B");

  var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

Does you third party replace sheets contained in the spreadsheet or the whole spreadsheet (source or target)? If the latter is the case, is the spreadsheetname and the folder where it is contained still the same and you have only one spreadsheet with this name inside the same folder? — ziganotschka
Try looking at the DriveApp reference. That should get you going. developers.google.com/apps-script/reference/drive/drive-app — a-burge
@ziganotschka it replaces the entire spreadsheet. That's correct yes, the file name stays exactly the same and there's only 1 file in that folder with that name. — Adam Newman

ziganotschka ziganotschka · Accepted Answer · 2020-06-11T10:47:00

You need to browse your Drive for a file with the given name and find out its ID

Sample for retrieving the ID of the source spreadsheet

  var fileName = "PASTE HERE THE NAME OF YOUR SOURCE SPREDSHEET";
  var file = DriveApp.getFilesByName(fileName).next();
  var sourceSpreadsheetID = file.getId();
  var sourceWorksheetName = "CustomSheetName1";
  var targetSpreadsheetID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
  var targetWorksheetName = "Sheet1";

  function importData1() {
    var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
    var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
    //var thisData = thisWorksheet.getDataRange();
    var thisData = thisSpreadsheet.getRangeByName("B:B");

    var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
    var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
    var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
    toRange.setValues(thisData.getValues()); 
  }

If it is the ID of the destination spreadsheet you want to retrieve - proceed respectively.

How to import data from another Google Sheet using Google Script without using Spreadsheet ID

1 Answers

You need to browse your Drive for a file with the given name and find out its ID