E
E
Epol Pers2019-10-10 17:09:36
JavaScript
Epol Pers, 2019-10-10 17:09:36

Google Sheets. How to avoid the error "Maximum number of characters in one cell: 50000"?

I am writing a site content parser in Google Sheets scripts and encountered the error "Maximum number of characters in one cell: 50000" Although in fact there are no more than 60 characters in one cell at the output.
The code:

function getConten(){
    getPageContent('https://xxx.ru/');  
}

function getPageContent(url) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("a1:g181");
  var cell = range.getCell(1,1);
  var cell2 = range.getCell(1,2);
  var cell3 = range.getCell(1,3);
  var cell4 = range.getCell(1,4);
  var cell5 = range.getCell(1,5);
  var cell6 = range.getCell(1,6);
  var cell7 = range.getCell(1,7);
  
  var test;
  
  var response = UrlFetchApp.fetch(url); 
  var textResp  = response.getContentText();
  var start, end, name;
  var i;
  for(i = 0; i < 50; i++){
    
    //Цена со скидкой   
    start = textResp.indexOf('<div class="price price-box" data-sale="sale:true">', end) + 51;
    end = textResp.indexOf('<em', start);
    name = textResp.substring(start, end);
    cell.setValue(name);
    cell = cell.offset(1,0);
    
     
    //Бренд
    start = textResp.indexOf('<h2>', end) + 4;
    end = textResp.indexOf('<span>', start);
    name = textResp.substring(start, end);
    cell2.setValue(name);
    cell2 = cell2.offset(1,0);

    //Название
    start = textResp.indexOf('<span>', end) + 7;
    start = textResp.indexOf('</span>',start) + 15;
    end = textResp.indexOf('</span>', start);
    name = textResp.substring(start, end);
    
    cell3.setValue(name);
    cell3 = cell3.offset(1,0);
    
    test = name.length;
    cell6.setValue(test);
    cell6 = cell6.offset(1,0);
    
    //
    start = textResp.indexOf('<span>', end) + 6;
    end = textResp.indexOf('</span>', start);
    name = textResp.substring(start, end);
    
    cell4.setValue(name);
    cell4 = cell4.offset(1,0);

    
     //Цена без скидки   
    start = textResp.indexOf('<div class="price price-box" data-sale="sale:true">', end) + 51;
     start = textResp.indexOf('<em',start) + 69;
    end = textResp.indexOf('<em', start);
    name = textResp.substring(start, end);    
    cell5.setValue(name);
    cell5 = cell5.offset(1,0);
    
    
  }
}

Thank you very much in advance!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Alexander Ivanov, 2019-10-14
@idx12x4xxx

You need to ensure that the total number of cells is less than 5 million, because this is a general restriction on data in Tables.

  1. An easy way to check the number of cells in use
  2. Remove redundant/empty. It's called "cut the table"

D
dollar, 2019-10-10
@dollar

site content parser in google sheets scripts

This is the main mistake.
Rent a hosting for 12 rubles per month (php+mysql), and run scripts there without restrictions. So you will save much more on nerves and time. A miser, as you know, pays twice.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question