CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-bigquery
Oksana Ok
Oksana Ok

Reputation: 555

Create Google App Function by using Big Query information

The current issue is to create a single source for the product price.

This is a link for what I'm looking for SAMPLE.

function DOUBLE(input) {
  return input * 2;
}

Actual steps:

Connect to big query and pull table which contains ( ProductID, ProductName, ProductPrice)

Create a function: What I have used with Google sheets

=ARRAYFORMULA(IFERROR(IFERROR(VLOOKUP(A1:A, DATA!A:C, 3, 0), 
                              VLOOKUP(A1:A, DATA!B:C, 2, 0))))

And now looking for 

function CHECK_CURRENT_PRICE(INPUT) {

  Pull ProductID, ProductName, ProductPrice WHERE ProductID = INPUT

  return ProductPrice;
}

So then I can go to Google Sheets and do like this

=CHECK_CURRENT_PRICE(8867)

And it will return 6.45

P.S. I will update this question when will get some more information regarding how to make this happens

ALTERNATIVE:

Create a function which will read product price from sheet "DATA" based on ProductID

E.G.

function CHECK(input) {
  Look where ProductID = input
  Get Product Price
  return ProductPrice;
}

UPDATE 2

function CHECK(INPUT) {
  var query = new google.visualization.Query('LINK TO GOOGLE SHEET');
  query.setQuery('SELECT C WHERE A = 2016');
  query.send(handleQueryResponse);
}



function handleQueryResponse(response) {
  // Called when the query response is returned.
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  return data

}

ERROR - ReferenceError: "google" is not defined.

Upvotes: 0

Views: 128

Answers (1)

Oksana Ok
Oksana Ok

Reputation: 555

function CHECK()
{
    var url = "SHEET LINK";
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText("UTF-8");
    var data = JSON.parse(json);
    var price = data;

    return price
}

Upvotes: 1

Related Questions