Perguntas[google-sheets](coding)

Estou tentando criar uma planilha de dados com as notas dos testes dos alunos. Há várias planilhas com notas que precisam ser formatadas condicionalmente com base em um intervalo — por exemplo: aprovado, reprovado, advertência. No entanto, o corte de notas varia de ano para ano e preciso atualizar cada fórmula manualmente.

Para resolver o problema de ter que voltar e alterar cada fórmula, gostaria de criar uma regra de formatação condicional reutilizável no Planilhas Google referenciando intervalos de pontuação de uma planilha separada para evitar atualizações manuais em várias planilhas de pontuação.

Sei que preciso usar INDIRETO para referenciar a outra planilha, mas não tenho certeza de qual fórmula devo usar para fazer os intervalos funcionarem sem ter que listar todas as pontuações possíveis do teste.

Fiz um mockup simplificado do meu problema aqui: Folha de Exemplo

Isso é possível fazer no Planilhas Google?

Tenho um conjunto de dados no Planilhas Google onde as datas aparecem na Coluna A e os valores (como nomes de itens) aparecem na Coluna B.

Os dados são assim:

Quero transpor os nomes dos itens horizontalmente ao lado de cada data —
ou seja, cada data terá seus valores correspondentes distribuídos nas Colunas C, D, E, etc.

Resultado esperado:

Tentei usar fórmulas como FILTER, TRANSPOSE, e ARRAYFORMULA,
mas só consigo obter resultados para o primeiro encontro ou resultados incompletos.

Como posso preencher dinamicamente todas as linhas conforme o resultado esperado?

De preferência usando uma fórmula, não scripts ou tabelas dinâmicas manuais.

Eu tentei, mas esta fórmula não é precisa

=IF(A2<>"", TRANSPOSE(FILTER(B$2:B, MMULT(N(ROW(A$2:A$1000)<=ROW(A2))*(A$2:A<>""), SEQUENCE(COLUMNS(B$2:B),1,1,0))=1)), "")

Desde já, obrigado!

Estou tentando destacar uma linha inteira onde há valores duplicados nas colunas (C) e (D) . Basicamente, cada agente (coluna D) deve ter um intervalo de datas exclusivo (coluna C). Essa combinação das colunas C e D deve ser sempre exclusiva e deve ser destacada/sinalizada.

Na formatação condicional, uma fórmula personalizada foi criada para realizar essa tarefa. Mas, quando a fórmula abaixo é adicionada, ela destaca toda a guia da planilha.

=(COUNTIF($A$2:$D$5000, $C2)>1)*(COUNTIF($A$2:$D$5000, $D2)>1)

Na imagem abaixo, forneci um exemplo de linhas que contêm entradas duplicadas nas colunas C e D e devem ser destacadas.

Vi esta publicação recente sobre o mesmo erro, e ela foi fechada porque o erro não pôde ser reproduzido. Recebi o mesmo erro ontem à noite e é facilmente reproduzível. Não sei qual a relação entre os dois, mas eis o que aconteceu comigo.

Eu estava tentando determinar a linha mais distante que é alcançável. Por exemplo, mesmo que sua planilha tenha apenas o padrão de 1000 linhas, você pode escrever =ROW(OFFSET(A1,9999,))para reach A10000. Ele retornará 10000. Depois de algumas tentativas e erros, descobri que poderia chegar ao ponto mais distante usando um OFFSET. aninhado.

=row(offset(offset(offset($A$1,B1,),C1,),D1,))

A última linha alcançável é a linha 2,147,483,647. Se eu tentasse ir para 2,147,483,649ou além, recebia um erro OFFSET, que avaliava um intervalo fora dos limites . Mas, logo antes disso, encontrei um buraco de minhoca. Isso mesmo, tentei alcançar a linha 2,147,483,648usando =row(offset(offset(offset($A$1,700000000,),700000000,),747483647,))e o que recebi foi -2,147,483,648. Você pode ver os resultados abaixo (a coluna E contém as caixas de seleção usadas para ativar/desativar as fórmulas).

Como um buraco de minhoca, ele vai para o outro lado do universo em folha. Eu poderia ir para a esquerda/direita naquela linha e até voltar para este lado, mas não consegui subir/descer nenhuma linha daquele lado. Estranho, sim, mas não prejudicial, e pretendo relatar isso.

No entanto, mais tarde, ao tentar abrir meu complemento pessoal, recebi o seguinte erro de execução. Exceção: Falha no Service Spreadsheets ao acessar o documento com ID ####. [linha: 77, função: myGlobalConfig, arquivo: core/lib/Config.

Depois de me esforçar para identificar o problema, tive a ideia maluca de verificar se o buraco de minhoca era a fonte. E era. Recebi o erro quando ativei aquela fórmula específica. O problema desapareceu quando desativei aquela fórmula.

Até criei uma função básica no script vinculado para recuperar o nome da planilha e usar o Toast para exibi-lo. Quando a fórmula do meio foi ativada, ocorreu o mesmo erro, gerando o valor da linha negativa. O erro desaparece quando a função é desativada.

Isso tem relação com a outra postagem mencionada anteriormente? Alguém sabe por que a linha está negativa?

Eu tenho um column Aque parece com:

11

11 11
5 6
5 5
5 6 6 5
11 11 11 11

6 7 8
3
3 2 3

e buscando uma maneira alternativa e mais inteligente de produzir TRUE/ FALSEconforme mostrado aqui:

A lógica é:

• FALSEse

  • a célula está vazia
  • não há números duplicados por célula

• TRUEse

  • a célula tem apenas um número
  • a célula tem dois ou mais números duplicados, independentemente da ordem

Mais notas:

• os números são separados por espaço
• os números podem variar de 1 a 80
• cada célula pode conter no máximo 40 números separados por espaço
• cada célula pode conter no mínimo 1 número ou estar vazia

Como posso resolver isso:

Posso somar (de várias maneiras - LAMBDA, QUERY(SUBSTITUTE(, SUM(SPLIT(, ...) e dividir por contagem. Se o resultado for um número inteiro TRUE, é , se o resultado tiver casas decimais, é , FALSEmas isso não funcionará para 3 2 3, 6 7 8.

Outra maneira seria dividi-lo e usar QUERYe contar números por linha, agrupar por row_numbers e pivotar por all_numbers, e LETentão envolvê-lo em IFe remover números menores que 2. Em seguida, combiná-lo com TRIM(TOCOL(QUERY(TRANSPOSE(e LEN. Então IF, como >0está, TRUEe adicionar o restante da lógica para casos não cobertos por QUERY.

A fórmula seria enorme de qualquer maneira, então estou procurando uma maneira "mais inteligente/curta" (se houver). Talvez haja algum padrão mágico de expressão regular com o qual eu não esteja familiarizado?

Quero contar um valor específico (no meu caso, quantos zeros existem) nos últimos x dias. Isso também deve ser feito dinamicamente, pois há dados adicionados todos os dias. É por isso que acho que preciso criar a fórmula dependente dos valores de data.

No momento, não posso fornecer uma planilha com um exemplo, mas o exemplo seria bem simples. A coluna A contém as datas , a coluna B contém os números , variando de 0 a 1.000.000. Quero contar os 0s dos últimos 7 dias, começando hoje.

O último que tentei usar foi:

=COUNTIFS(B4:B975;A4:A975;"<="&TODAY();A4:A975;">="&TODAY()-7;"0")

Também experimentei um pouco, mas o erro sempre me diz que ou os valores internos têm tamanhos diferentes ou precisam estar em pares. Estou usando algo semelhante para MÉDIASSE, que funciona perfeitamente, então pensei que poderia usá-lo também para CONT.SE com alguns pequenos ajustes, mas acho que não é tão simples assim.

Estou usando atualmente

=COUNTIFS($A$4:$A$975;">="&TODAY()-7;$B$4:$B$975;"0")

mas é uma fórmula quebrada, já que também conta todos os 0 no futuro e também não tive sucesso em ajustá-la para que funcionasse como expliquei.

Por exemplo, se eu tiver uma tabela mais ou menos assim:

Quero conseguir pesquisar "Cobre" e obter seus dois índices correspondentes (P2, R2). Preciso conseguir obtê-los separadamente, então provavelmente uma fórmula para obter o "P" e outra para obter o "R". Obter os índices numéricos e alfabéticos no Planilhas Google e, em seguida, obter os "nomes" dos índices de lá provavelmente seria a melhor aposta, pelo que sei.

Tentei usar vlookup, hlookup, query, match, index e qualquer outra coisa que eu pudesse encontrar online, mesmo que vagamente relacionada, mas sem sucesso. O problema que encontrei com todas essas ferramentas é que elas só querem pesquisar uma linha ou coluna por vez, o que significa que, na melhor das hipóteses, eu precisaria de pelo menos 20 fórmulas diferentes para pesquisar cada linha, uma por uma.

Obrigado a todos pela ajuda!

Estou tentando criar uma planilha do Google que eu possa usar para agendar um calendário de manutenção de oficina.

Eu segui o tutorial abaixo, mas estou entendendo algo errado. Espero que alguém consiga identificar o erro facilmente.

https://www.youtube.com/watch?v=fAfomDR0pe4&list=PL3ooKofg5LEr7yYPz7DyReKrAhhBecfUv

Este erro aparece e tentei descobrir o motivo, mas não consegui. Formatei a hora de início e a hora de término para "data e hora" e tentei remover todas as células, mantendo apenas o que estou inserindo. Acho que posso ter inserido os intervalos incorretamente? Não tenho certeza...

Exceção: Os parâmetros (String,String,null,(class)) não correspondem à assinatura do método para CalendarApp.Calendar.createEvent.

Código de script abaixo:

let calendarId = '[email protected]'; // add your calendar id here

// if your events are coming through but the time is off, click on the gear icon to the left and make sure the time zone matches the time zone setting on your Google Calendar

// on open script menu
function onOpen() {
    let ui = SpreadsheetApp.getUi();
    ui.createMenu("Chur Bro").addItem("Update Calendar", "updateCalendar").addToUi();
}

// main function will irterate through data and determine if we are adding, updating or deleting an event.
function updateCalendar() {
    let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName("Service Register");
    let events = sheet.getRange(9,13,sheet.getLastRow()-1,24).getValues();

    for (i = 0; i < events.length; i++) {
        let event = events[i];
        let returnedData;

        if (event[0] != '') {
            switch (event[0]) {
                case 'Add':
                    returnedData = addEvent(event)
                    break;
                case 'Update':
                    returnedData = updateEvent(event);
                    break;
                case 'Delete':
                    returnedData = deleteEvent(event);
                    break;
            }
            sheet.getRange(i + 9, 21, 9, ).setValues([returnedData]);
        }
    }
    sheet.getRange(9, 13, sheet.getLastRow()-1, 13).clearContent();
}


function addEvent(event) {

  let title = event[17];
  let description = event[18];
  let location = event[19];
  let emails = event[20];
  let startTime = event[23]
  let endTime = event[24]
  let options = {};

  if (description != '') { options['description'] = description; }
  if (location != '') { options['location'] = location; }
  if (emails != '') { options['guests'] = emails; }

  let calendar = CalendarApp.getCalendarById(calendarId);
  let id = calendar.createEvent(title, startTime, endTime, options).getId();
  return ["Added", id];

}




function updateEvent(event) {

    let title = event[17];
    let description = event[18];
    let location = event[19];
    let emails = event[20];
    let eventId = event[22];
    let startTime = event[23];
    let endTime = event[24];

    let calendar = CalendarApp.getCalendarById(calendarId);
    let thisEvent = calendar.getEventById(eventId);
    thisEvent.setTime(startTime, endTime)
        .setTitle(title)
        .setDescription(description)
        .setLocation(location);

    if (emails != '') {
        emails = emails.split(",");
        emails.forEach(x => thisEvent.addGuest(x));
    }
    return ["Modified", eventId];
}




function deleteEvent(event) {

    let eventId = event[22];
    let calendar = CalendarApp.getCalendarById(calendarId);
    let thisEvent = calendar.getEventById(eventId);
    thisEvent.deleteEvent();
    return ['Deleted', ''];

}

Imagem da Planilha Google:

Planilha Google

IMAGEM DA PLANILHA DO GOOGLE

Quais seriam as fórmulas de matriz em A1, B1, C1 e D1 (separadamente) para fornecer as colunas de saída desejadas, conforme mostrado.

Link da Planilha Google

OBSERVAÇÃO: A diferença entre "Cabeçalhos Principais" (PARENTES, AMIGOS, AMIGOS DE NEGÓCIOS) e "nomes" é que os "Cabeçalhos Principais" têm 1 letra de espaço no início e no fim.

As colunas A, B e C são todas iguais, a única diferença é que para os "Títulos Principais" a coluna A fornece alfabetos, a coluna B fornece números e a coluna C fornece algarismos romanos.

CONDIÇÃO PARA NUMERAÇÃO SERIAL:
(1) A numeração serial é feita completamente com base nas colunas: NOMES E PAX.
(2) Se PAX for 0, a saída estará em branco.

PARA as colunas A, B, C:
Quando PAX estiver em branco:
-> se for um "Título principal", a saída será um alfabeto OU
-> se for um "nome", a saída estará em branco.

PARA a coluna D:
Quando PAX estiver em branco:
-> se for um "Título principal", a saída estará em branco OU
-> se for um "nome", a saída será uma numeração serial até o próximo "Título principal", depois reiniciará de 1 até o próximo "Título principal" e assim por diante.

EDIT:
As colunas NAMES e PAX são entradas, o restante é a saída necessária.

Dados:

Na coluna AI, há fileiras de caixas de seleção e na coluna I, haverá "Não Imprimir" ou "Em Branco". Células em branco devem ativar a caixa de seleção correspondente e "Não Imprimir" deve mantê-la desativada. Se a linha estiver em branco, a caixa de seleção também deve ser desativada (ou removida). Quero que isso seja uma função regular, não uma função de edição. Não posso usar uma fórmula porque quero a capacidade de substituir a caixa de seleção, se necessário.

Tenho tentado modificar a fórmula abaixo, mas sem sucesso. Acho que ela precisa percorrer todas as linhas da Coluna 1 e retornar verdadeiro ou falso.

function autoCheck() {
 const sheet = SpreadsheetApp.getActiveSheet();
 const sName = sheet.getName();
 const iRange = sheet.getRange('I2:I101')
// sheet.getRange('A2:A101').removeCheckboxes();

 if (sName === "sheet1") {
const len = sheet.getRange('I2:I101').getValues().filter(row => row[0] != 'Do Not 
Print').length
  const iRange = sheet.getRange(9,1,len,1);
 // sheet.getRange('A2:A101').removeCheckboxes();
 // range.insertCheckboxes().uncheck();
 Logger.log(iRange);
if (iRange.getValues()[0][0] === "") {
  sheet.getRange('A2:A101').uncheck();
  iRange.check();
}
}

}