2

I am learning how to code so sorry if this is too basic, but I am getting troubles here:

I've been trying to invoke the Google Natural Language API, to give me information about information on 210 rows of my Google Spreadsheet (the whole table has 211 rows). I would like to save the results on 1 Json File.

I am trying to run a loop with the code below, but I am getting the Json file only with the information corresponding to the 1st row. Tried as well to put the "Driveapp.createFile line of code" inside of the loop function, but then I have many Json files, each one with the information corresponding to one row. And what I would like is 1 Json file, with the corresponding information of the 210 rows.

I would appreciate your help, please.

    function analyzeText() {


  var client = "Spreadsheet_ID";
  var query = SpreadsheetApp.openById(client).getSheetByName("1. Template");
 var result = SpreadsheetApp.openById(client).getSheetByName("Teste - Natural Language API");
 var lrow = query.getLastRow();

  for(var i=2; i<=211;i++)
  {
  var text = query.getRange(i,211).getValue()

  var requestUrl = [
    'https://language.googleapis.com/v1beta2/documents:analyzeEntities?key=',
    'API_KEY_XXXXXXXXXXXXXXXXXXX'
  ].join("");


  var data = {
    "document": {
      "language": "en-us",
      "type": "PLAIN_TEXT",
      "content": text
    },
    "encodingType": "UTF8"
  };

  var options = {
    method : "POST",
    contentType: "application/json",
    payload : JSON.stringify(data)
  };

  var response = UrlFetchApp.fetch(requestUrl, options);

  var data = JSON.parse(response);

  }
 DriveApp.createFile('response3.json', response, MimeType.PLAIN_TEXT);

}
Luís Correia
  • 27
  • 1
  • 5
  • Can I suppose that your request works and the requests more than 200 to `https://language.googleapis.com/v1beta2/documents:analyzeEntities` works without error? – Tanaike May 10 '20 at 23:52
  • Yes, why do you ask? Did you have any problem? – Luís Correia May 10 '20 at 23:53
  • Thank you for replying. About `why do you ask? Did you have any problem?`, I cannot test the request. So I asked about them. I deeply apologize for this. – Tanaike May 10 '20 at 23:54
  • No problem, mate! I was asking because I could help :) – Luís Correia May 11 '20 at 00:03
  • Thank you for replying. Your request works and the requests more than 200 to https://language.googleapis.com/v1beta2/documents:analyzeEntities works without error. If my understanding is correct, can I ask you about the sample result you expect? I think that it is required to create an array including JSON object. How about this? And `data` of `var data = JSON.parse(response);` is an JSON object? I would like to think of the solution after I could correctly understand about your situation and goal. I apologize for this. – Tanaike May 11 '20 at 00:39

1 Answers1

0

I would suggest you instead of the approach you are taking (using a for loop and the method getValue(), which it's a slow method to call in a loop), consider this one I am giving you with this code:

function analyzeText() {
  var clientId = "your-sheet-id";
  var ss =  SpreadsheetApp.openById(clientId);
  var templateSheet = ss.getSheetByName("1. Template");
  // .getRange(row, column, numRows) -> From the first row and col, take the next 4 rows
  // Modify these arguments depending in where you want to start and how many rows you want
  var data = templateSheet.getRange(1, 1, 4).getValues();
  // You will get an array 2D, using join you will able to get an string from
  // all the elements in that array
  var text = data.join();
  var requestUrl = [
    'https://language.googleapis.com/v1beta2/documents:analyzeEntities?key=',
    'API_KEY_XXXXXXXXXXXXXXXXXXX'
  ].join("");
  // Now text will have all your cell values and you only need to do one request
  var data = {
    "document": {
      "language": "en-us",
      "type": "PLAIN_TEXT",
      "content": text
    },
    "encodingType": "UTF8"
  };
  var options = {
    method : "POST",
    contentType: "application/json",
    payload : JSON.stringify(data)
  };
  var response = UrlFetchApp.fetch(requestUrl, options);
  var data = JSON.parse(response);
  DriveApp.createFile('response3.json', response, MimeType.PLAIN_TEXT);
}

In this way, you only need to make one request and it will be faster than running 211 times your loop. I would also recommend you to check:

  • Apps Script Quotas: Running your code as you have it, it would give you more chances of hitting these quotas.

  • Best Practices: You can check more about the best practices so you can have a better idea about why I was telling you to avoid the getValue() method in a loop.

alberto vielma
  • 2,302
  • 2
  • 8
  • 15