0

I have a Spreadsheet where I have used IMPORTRANGE to pull data from another spreadsheet. I also have a script that always gets the last row of specific columns, fills a form, create a link and send it to someone.

I'm trying to use getValues() to get the value of a specific cell that has been filled with data from IMPORTRANGE function.

function askForPrice() {

var ss = SpreadsheetApp.openById('xXx');
var sheet = ss.getSheetByName('Sheet1');

var range1 = ss.getRange('A1').getDataRegion().getLastRow();
var data = ss.getDataRange().getValues();

var formUrl = ss.getFormUrl();     
var form = FormApp.openByUrl('xXxXx');
var items = form.getItems();

var dateCol = 1
var myID = sheet.getRange(range1, dateCol).getValue();
var dataCol1 = sheet.getRange(range1, dateCol).getValue();

Logger.log(dataCol1)

The problem is that after I run the code and use Logger.log() to check what value is captured by getValues(), nothing appears. If the content is typed directly into the cell, I am able to return it through Logger.log(). But if the cell is contained in the IMPORTRANGE range, it doesn't appear in Logger.log().

What should I do to get this to work, so the value of the cell would be returned?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Marco Fernandes
  • 11
  • 1
  • 1
    In order for us to understand the value of `var range1 = ss.getRange('A1').getDataRegion().getLastRow();` then we have to see the spreadsheet. – Cooper Jul 04 '19 at 04:23
  • 1
    As @Cooper said, we need to see the spreadsheet to better understand the problem. However, I don't understand what you are doing with range1, `getRange('A1')` will get the first cell, `getDataRegion()` will return the same cell (if it's not empty) and lastly, `getLastRow()` will return 1 (because you're still on the first cell). When you get `myID` and you put `...getRange(range1, dateCol).getValue()` it is the same as if you had written `ss.getRange('A1').getValue()`, because range1 abd dateCol are both 1 here. Lastly, you're not using `data` for anything. – AMolina Jul 04 '19 at 07:39

2 Answers2

2

AFAIK formulas are calculated by the Google Sheets UI. Considering this I don't recommend to read spreadsheet formula results when the spreadsheet isn't opened by a user.

One workaround is that the IMPORTRANGE results be saved as values when the spreadsheet is opened by a user at some point on your workflow.

Another, and maybe more reliable, is to use Google Apps Script to do the import job instead on relying on IMPORTRANGE.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
1

Requirement:

Get value of cell using IMPORTRANGE formula.

Solution:

Instead of using getValues(), use getDisplayValues(). This returns the displayed value of the cells in a 2d array.

Your line of code would look something like this:

var data = ss.getDataRange().getDisplayValues();

Reference:

ross
  • 2,684
  • 2
  • 13
  • 22