CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-drive-api
Adam Newman
Adam Newman

Reputation: 59

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

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()); 
}

Upvotes: 1

Views: 7711

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

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.

Upvotes: 1

Related Questions