I need to search a value in an array of more then 8 thousand registers.
I've used indexOf but i think the array is to large and some pagination is occurring, so the value I know is in the array is not found.
Any ideas on how to get around it and search such a large array?
Below is my code.Its intended to search a value on an array and decide if it's not there it's new and should be added if it's found it should update/edit the value on destination.
That's an example of a value that i'm searching on the array image array example
function etlDIT() {
var tabelaorigem = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Lista Chamados - DIT');
var tabelasquad = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Squad N2');
var data = tabelaorigem.getDataRange().getValues();
var datasquad = tabelasquad.getDataRange().getValues();
var novoValorStatusKanban = ""
var finalizacao = ""
var baseSistema = listabaseSistema()
for (var i = 0 ; i < data.length; i++){
console.log(data[i])
if (i > 0) {
for (var e = 0; e < datasquad.length; e++ ){
if(e > 0){
var chave = (datasquad[e][0]+','+data[i][4])
var squad = pesquisaSquad(chave,'Squad x Projeto')
if(squad != ""){break}
}
}
var registroNovo = baseSistema.indexOf(data[i][0]);
if (registroNovo == -1){
if (data[i][6] == "Concluído" || data[i][6] == "Cancelada"){
novoValorStatusKanban = data[i][6] == "Cancelada" ? "Cancelado": data[i][6];
}
else {novoValorStatusKanban = ""}
var atualizaStatusKanban = novoValorStatusKanban == "" ? "":novoValorStatusKanban;
finalizacao = "";
finalizacao = data[i][9] == "NULL" ? "": data[i][9];
var usuarioDit = pesquisaUsuarioDIT(data[i][5],'Usuários')
var vencimento = data[i][8] == "NULL" ? "": data[i][8];
var linha = [data[i][0],data[i][1],data[i][2],data[i][3],data[i][4],data[i][5],data[i][6],data[i][7],vencimento,atualizaStatusKanban,"1000","",finalizacao,"","",usuarioDit,"Não","","","","",squad];
adicionaChamado(linha);
}
else {
if (data[i][6] == "Concluído" || data[i][6] == "Cancelada"){
novoValorStatusKanban = data[i][6] == "Cancelada" ? "Cancelado": data[i][6];
}
else {novoValorStatusKanban = ""}
var atualizaStatusKanban = novoValorStatusKanban == "" ? "":novoValorStatusKanban;
finalizacao = "";
finalizacao = data[i][9] == "NULL" ? "": data[i][9];
var usuarioDit = pesquisaUsuarioDIT(data[i][5],'Usuários')
var vencimento = data[i][8] == "NULL" ? "": data[i][8];
var linha = [data[i][0],data[i][1],data[i][2],data[i][3],data[i][4],data[i][5],data[i][6],data[i][7],vencimento,atualizaStatusKanban,"1000","",finalizacao,"","",usuarioDit,"Não","","","","",squad];
atualizaChamado(linha);
}
}
console.log('perc:' + (i/data.length)*100 + ' chamado: '+data[i][0])
}
}
function pesquisaUsuarioDIT(valor,planilha) {
var activeSheet = SpreadsheetApp.getActive().getSheetByName(planilha).getRange('E2:E');
var planilhaTotal = SpreadsheetApp.getActive().getSheetByName(planilha).getDataRange().getValues();
var textSearch = activeSheet.createTextFinder(valor).findAll();
if (textSearch.length > 0) {
var row = textSearch[0].getRow()-1;
var usuarioDit = planilhaTotal[row][0];
return usuarioDit;
}
else {
return "";
}
}
function pesquisaSquad(chave,planilha) {
var activeSheet = SpreadsheetApp.getActive().getSheetByName(planilha).getRange('C2:C');
var planilhaTotal = SpreadsheetApp.getActive().getSheetByName(planilha).getDataRange().getValues();
var textSearch = activeSheet.createTextFinder(chave).findAll();
if (textSearch.length > 0) {
var row = textSearch[0].getRow()-1;
var squadN2 = planilhaTotal[row][0];
return squadN2;
}
else {
return "";
}
}
function pesquisaChamado(chamado) {
const appAccessKey = '';
const appId = '';
const table = '';
const action = 'Find';
const properties = {
'Locale': 'en-US',
'RunAsUserEmail': ''
};
const rows = [{"Chamado": chamado}];
const body = {
'Action': action,
'Properties': properties,
'Rows': rows
};
const payload = JSON.stringify(body);
const url = 'https://api.appsheet.com/api/v2/apps/' + appId + '/tables/' + table + '/Action';
const method = 'post';
const headers = {'ApplicationAccessKey': appAccessKey};
const params = {
'method': method,
'contentType': 'application/json',
'headers': headers,
'payload': payload,
'muteHttpExceptions': true
};
const requestSimulate = UrlFetchApp.getRequest(url, params);
let response
try{
response = UrlFetchApp.fetch(url, params);
}
catch(err){
Logger.log('err: ' + err);
}
finally{
}
var data = response.getContentText();
var resultListagem = JSON.parse(data);
return resultListagem;
}
function adicionaChamado(linha) {
const appAccessKey = '';
const appId = '';
const table = '';
const action = 'Add';
const properties = {
'Locale': 'en-US',
'RunAsUserEmail': ''
};
const rows = [{"Chamado": linha[0],
"Título": linha[1],
"Entidade":linha[2],
"Sistemas":linha[3],
"Projeto":linha[4],
"Solicitante":linha[5],
"Status":linha[6],
"Criação":linha[7],
"Vencimento":linha[8],
"Status Kanban":linha[9],
"Prioridade":linha[10],
"Analista Cliente":linha[15],
"Chamado Triado":linha[16],
"Squad N2":linha[21]
},
];
const body = {
'Action': action,
'Properties': properties,
'Rows': rows
};
const payload = JSON.stringify(body);
const url = 'https://api.appsheet.com/api/v2/apps/' + appId + '/tables/' + table + '/Action';
const method = 'post';
const headers = {'ApplicationAccessKey': appAccessKey};
const params = {
'method': method,
'contentType': 'application/json',
'headers': headers,
'payload': payload,
'muteHttpExceptions': true
};
const requestSimulate = UrlFetchApp.getRequest(url, params);
let response
try{
response = UrlFetchApp.fetch(url, params);
}
catch(err){
Logger.log('err: ' + err);
}
finally{
Logger.log ('requestSimulate:');
Logger.log (requestSimulate);
Logger.log ('response: ' + response);
}
}
function atualizaChamado(linha) {
const appAccessKey = '';
const appId = '';
const table = '';
const action = 'Edit';
const properties = {
'Locale': 'en-US',
'RunAsUserEmail': ''
};
const rows = [{"Chamado": linha[0],
"Projeto":linha[4],
"Status":linha[6],
"Squad N2":linha[21]
},
];
const body = {
'Action': action,
'Properties': properties,
'Rows': rows
};
const payload = JSON.stringify(body);
const url = 'https://api.appsheet.com/api/v2/apps/' + appId + '/tables/' + table + '/Action';
const method = 'post';
const headers = {'ApplicationAccessKey': appAccessKey};
const params = {
'method': method,
'contentType': 'application/json',
'headers': headers,
'payload': payload,
'muteHttpExceptions': true
};
const requestSimulate = UrlFetchApp.getRequest(url, params);
let response
try{
response = UrlFetchApp.fetch(url, params);
}
catch(err){
Logger.log('err: ' + err);
}
finally{
}
}
function listabaseSistema() {
const appAccessKey = '';
const appId = '';
const table = '';
const action = 'Find';
const properties = {
'Locale': 'en-US',
'RunAsUserEmail': ''
};
const rows = [];
const body = {
'Action': action,
'Properties': properties,
'Rows': rows
};
const payload = JSON.stringify(body);
const url = 'https://api.appsheet.com/api/v2/apps/' + appId + '/tables/' + table + '/Action';
const method = 'post';
const headers = {'ApplicationAccessKey': appAccessKey};
const params = {
'method': method,
'contentType': 'application/json',
'headers': headers,
'payload': payload,
'muteHttpExceptions': true
};
const requestSimulate = UrlFetchApp.getRequest(url, params);
let response
try{
response = UrlFetchApp.fetch(url, params);
}
catch(err){
Logger.log('err: ' + err);
}
finally{
}
var data = response.getContentText();
var resultListagem = JSON.parse(data);
return resultListagem;
}
just got it!!! The variable that i wanna find is a number so i just had to cast it as a string using .toString() before i search on the array!