CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets-api
Laurent
Laurent

Reputation: 1563

Google Sheets API / JS: find a value in sheet (anywhere)

I'm exporting data from Google Analytics to put it in Google Sheets. I have created a sheet called "raw" where I will dump all the data. In order to avoid exporting and dumping the data, I need to check if the data is already there. I don't need to know where it is, I just need to know where it is.

I have written a function to get the data out of Google Analytics and it works as when I display the output in the logs, I get what I need.

result = getData(tableId,dimensions,filters,'2021-01-01','2021-01-01',metrics);
Logger.log(result['rows']);

Now I want to loop through those results and check if the data is already available in the sheet. If it's not yet available, I would add it to the bottom of the sheet

var raw = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("raw");
var raw_content = raw.getDataRange();

From here, I used the script available here: How to check if the value exist in google spreadsheet or not using apps script

for (var b=0;b<result['rows'].length;b++)
          {
            temp_date = result['rows'][b][0];
            temp_value = result['rows'][b][1]; 
            unique_id = label+'_'+temp_date;
            Logger.log(unique_id);

            var textFinder = raw_content.createTextFinder(unique_id);
            var occurrences = textFinder.findAll().map(x => x.getA1Notation());

            if (occurrences == []) 
              {
                Logger.log('not found');
              }
            else 
              {
                Logger.log('found');
              }

          }

Even if the sheet is empty, it always return "found". If I add manually one of the unique ids, it still returns "found" all the time.

I have also tried the second alternative in this post but it doesn't work.

I'd like to avoid using a loop to check cells one by one because at some point, there will be a lot of data in there.

Any idea?

thanks

Upvotes: 0

Views: 442

Answers (1)

CMB
CMB

Reputation: 5163

From your code and what you are trying to accomplish, you would need to define textFinder for each iteration of the loop, with different values of label+'_'+temp_date.

Update: Two arrays in JavaScript cannot be compared with == as it would always return false. The best way in this case is to check its length if zero or not.

for (var b=0;b<result['rows'].length;b++)
  {
    temp_date = result['rows'][b][0];
    temp_value = result['rows'][b][1]; 
    unique_id = label+'_'+temp_date;

    var textFinder = raw_content.createTextFinder(unique_id);
    var occurrences = textFinder.findAll().map(x => x.getA1Notation());

    if (occurrences.length) 
       {
         Logger.log('found');
       }
    else 
       {
         Logger.log('not found');
       }
  }

Sample Result:

enter image description here

Reference:

Class TextFinder

Upvotes: 1

Related Questions