CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Awais Anwar
Awais Anwar

Reputation: 31

How to get results of a custom google sheet function in cells

I am trying to get results of following function in cells in google sheet. I can get the results in script log but i need it in cells .. can someone add to this syntax so that its results can be placed in cells.

function scrapeDemo(url) {

  var html = UrlFetchApp.fetch(url).getContentText();
  var res = html.match(/<div class="cr_data rr_stockprice module">.+?(<table .+?<\/table>)/);

  var document = XmlService.parse(res[1]);
  var root = document.getRootElement();

  var trNodes = root.getChild('tbody').getChildren();
  trNodes.forEach((trNode) => {
    var tdNodes = trNode.getChildren();
    var fieldName;
    var fieldValue;
    tdNodes.forEach((tdNode, idx) => {
      if (idx % 2 === 0) {
        fieldName = tdNode.getValue().trim();
      } else {
        fieldValue = tdNode.getValue().trim();
        console.log( fieldName + " : " + fieldValue );
      }
    } );
      } );
    }

I am trying to get the result that are currently available in script log in cells. When I put scrapeDemo(url) in a cell It should give the data. It can be as a table with Fieldnames in one column and Fieldvalue in next column.

Upvotes: 0

Views: 360

Answers (2)

Wicket
Wicket

Reputation: 38140

The script in the question body doesn't include a return statement, but A Google Sheets custom function should have one returning a value / object supported by Google Sheets (number, string, boolean, date, or an Array of Arrays of supported values/objects), example:

/**
 * If a and b are numbers these values will be added
 * If a or b are strings the these values will be concatenated
 */
function myFunction(a,b){
  return a + b;
}

/**
 * Returns a Matrix of X having rowNum by colNum
 *
 */
function XMATRIX(rowNum,colNum){
  var output = [];
  for(var i = 0; i < rowNum; i++){
    output[i] = [];
    for(var j = 0; j < colNum; j++){
       output[i][j] = 'X';
    }
  }
  return output
}

Resources

https://developers.google.com/apps-script/guides/sheets/functions

Upvotes: 2

Malte K.oder
Malte K.oder

Reputation: 85

If you have a range or want to build a range of data and you are executing your script in a google sheet, you can do it like:

var sheet = SpreadsheetApp.getActive().getSheetByName("NameOfTheSheet");
sheet.getActiveCell("A1").setValues(valueRange);

If you want to write value by value on its own. Use:

var sheet = SpreadsheetApp.getActive().getSheetByName("NameOfTheSheet");
sheet.setActiveRange("A1").setValues(value);

if you want to iterate in the Cells, then use:

...
sheet.setActiveRange("A" + i).setValues(value);
i++;
...

Upvotes: 2

Related Questions