Perguntas[google-apps-script](coding)

Estou tentando atualizar uma célula com a data e a hora em que um intervalo específico de células foi editado.

Estou mexendo no código a seguir, mas depois de verificar o log de execução, o gatilho falhou em todas as ocasiões.

Onde estou errando?

function onEdit(e) {
  var range1 = e.range.getSheet().getRange("D260:D264");
  var range2 = e.range.getSheet().getRange("P260:P264");
  var range3 = e.range.getSheet().getRange("S260:S264");
  var range4 = e.range.getSheet().getRange("AE260:AE264");
  var range5 = e.range.getSheet().getRange("AJ260:AJ264");
  var range6 = e.range.getSheet().getRange("D53");
  var range7 = e.range.getSheet().getRange("N53");

  if (e.range.intersects(range1) || e.range.intersects(range2) || e.range.intersects(range3) || e.range.intersects(range4) || e.range.intersects(range5) || e.range.intersects(range6) || e.range.intersects(range7)) {

  var dateCell = e.range.getSheet().getRange("W247"); 

  dateCell.setValue(new Date());

  }

}

Tenho um script que pega a entrada de uma conexão JDBC e a grava em uma planilha. Há momentos em que o resultado dessa operação não é o esperado, então espalhei código como este em pontos-chave do script:

          Logger.log("Trying to delete rows, received: "
                      +  e.message) ;
              Logger.log("   Continuing.") ;

Quando executo esse código a partir do botão RUN do editor de script, consigo ver o log de execução mais recente sem esforço - está bem ali na minha frente. Mas quando executo o mesmo código a partir de um menu criado assim:

function onOpen() {
    var menuItems ;
    var spreadsheet = SpreadsheetApp.getActive();
    var user = Session.getEffectiveUser().getEmail() ;
    var validUsers = [ validUsersList ]

    if (spreadsheet.getDataRange().canEdit()) {
        menuItems = [
            {name: 'Process all streams' , functionName: 'processAllStreams'}
            , {name: 'Build covered artists'           , functionName: 'rebuildCoversSheet'}
            //, {name: 'Add to song list'              , functionName: 'buildSongList'}
            //, {name: 'Process selective streams'       , functionName: 'processStreams'}
            //, {name: 'Move suggestions to top'         , functionName: 'moveSuggestionsToTop'}
            , {name: 'Go to bottom'                    , functionName: 'showBottomRange'}
            , {name: 'New livestream details'          , functionName: 'getNewLivestreamDetails'}
                ];
        spreadsheet.addMenu('Process it', menuItems);

             etc.

Quando executado a partir desse menu, não vejo o log. Fui ao editor e verifiquei lá; ele não parece saber sobre scripts que foram executados fora do ambiente de desenvolvimento.

O que devo fazer para ver esses logs? Percebi que há uma opção de registro em nuvem, mas parece que não é uma configuração tão simples. E os documentos não dizem nada sobre as limitações de execução de scripts via menu ou via editor de scripts. A documentação menciona algumas limitações:

These logs are intended for simple checks during development and debugging, and do not persist very long.

Mas eles não parecem abordar essa questão. Existem abordagens fáceis?

A propósito, executar a partir do editor de script não é uma opção para algumas das coisas que estão sendo feitas. O motivo é que há prompts e diálogos (getUi.prompt() etc) envolvidos, e eles não são permitidos a partir do modo Editor de Script. Então, no momento, é um problema.

EDIT - adicionado um script testável

O código a seguir:

function onOpen() {
    var menuItems = [
            { name: 'Make a log entry', functionName: 'makeEntry' }
        ];
        SpreadsheetApp.getActive().addMenu('Process it', menuItems);
}

function makeEntry() {
  Logger.log('here you go....') ;
}

Se você criar uma nova Planilha Google e adicionar este código ao editor de script:

Ao executar o script no editor de scripts, você verá a entrada de log criada.

Ao executar o script do menu criado ("Processar"), não consigo encontrar essa entrada de log.

Estou criando um Formulário Google via Apps Script e preciso definir um campo para aceitar apenas números de telefone de 10 dígitos dos EUA. Encontrei esta expressão regular que funciona se eu defini-la via Form Editor: ^(\+0?1\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$.

Mas, novamente, preciso que isso funcione no Apps Script. Meu trecho de código é o seguinte:

let form = FormApp.create("new Order");
let phoneNumber = form.addTextItem().setTitle("Textable phone number").setRequired(true);
  phoneNumber.setValidation(FormApp.createTextValidation()
      .requireTextMatchesPattern("^(\+0?1\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$")
      .build());

Isso gera Exception: Invalid data updating form.Quando eu altero para .requireTextMatchesPattern(/^(\+0?1\s)?\(?\d{3}\)?[\s.-]\d{3}[\s.-]\d{4}$/)o script é executado e concluído, mas então é impossível preencher o formulário porque um número de telefone padrão gera um erro dizendomust match pattern.

Então, como posso definir um padrão de expressão regular no Apps Script que permitirá a entrada corretamente?

Sou relativamente novo no Google Apps Script, mas estou aprendendo rápido, e agora estou tentando chamar uma função com um menu, no entanto, não consigo descobrir como atribuir variáveis ​​naquele item de menu. Meu código é algo como isto:

ui.createMenu('foo')
  .addItem('bar', 'foobar')

function foobar(bar) {
  if (bar == 1) {
    //something
  }
  else if (bar == 2) {
    //something else
  }
}

Estou tentando atribuir 'bar' dentro de '.addItem('bar', 'foobar')'

Objetivo:
Extrair dados de: https://www.olg.ca/en/home.html

Valor do prêmio máximo do LottoMax e número máximo de milhões
Valor da bola de ouro do Lotto649
Valor do prêmio máximo do Lotto649

Meu problema:
Os valores são renderizados dinamicamente em JavaScript, então não consigo usar ImportXML. Criei uma função no Apps Script, mas não vejo uma API para buscar os dados, então não vai funcionar.

Existe algum outro método que eu possa usar para buscar esses dados? Qualquer insight será muito apreciado. Obrigado.

Tenho uma macro de aplicativo de planilha do Google que está comparando pontuações semanais em um conjunto de times em uma liga. Ela está verificando qual time teria vencido um ao outro a cada semana comparando pontuações em um conjunto de categorias. Há alguma lógica para definir se algumas categorias pontuam por serem mais altas ou mais baixas. A macro está comparando corretamente as pontuações em quase todos os casos, mas eventualmente encontrei um bug que não consigo resolver.

  // Get all relevant data from the stat sheet in one go
  const statsRange = statSheet.getRange('C8:C234').offset(0, weeks);
  const statsData = statsRange.getValues();

  // Iterate through teams (assuming 12 teams)
  for (let i = 0; i <= 11; i++) {
    for (let j = 0; j <= 11; j++) {
      if (i===11&&j===11) {break}
      if (j===i) {j=j+1;}
      let score = 0;

      // Iterate through stats for each team pair, comparing and calculating score. 
     Scores are across 19 rows per team in the data set. There are 15 relevant rows. 

      for (let k = 0; k <= 15; k++) { 
        var homeStat = statsData[(19 * i) + k]; 
        var awayStat = statsData[(19 * j) + k];

        // Skip some stats and handle some differently
        if (k === 0 || k === 1 || k === 3 || k === 4) {continue}
        else if (k === 13||k === 14) {
          score += (homeStat < awayStat) ? 1 : (homeStat > awayStat) ? -1 : 0;
        } else {
          score += (homeStat > awayStat) ? 1 : (homeStat < awayStat) ? -1 : 0;
        }
        console.log(i, j, k, homeStat, awayStat, score)
      }

Um exemplo de conjunto de dados de entrada

Por algum motivo, se HomeStat e AwayState forem de ordens de magnitude diferentes, essa comparação produz resultados inversos. Um exemplo de log/saída: i e j são times na planilha. k é a categoria de estatísticas. Estatísticas de casa e visitante são as entradas de cada time para a categoria. A pontuação deve aumentar ou diminuir com base na lógica. k = 10 é o erro. Ele aumenta quando a pontuação da casa é menor que a pontuação do visitante.

|time|log|i|j|k|home stat|away stat|score|
6:59:04 PM  Info    11 10 2 [ 0.476 ] [ 0.44 ] 1
6:59:04 PM  Info    11 10 5 [ 0.793 ] [ 0.77 ] 2
6:59:04 PM  Info    11 10 6 [ 35 ] [ 68 ] 1
6:59:04 PM  Info    11 10 7 [ 360 ] [ 542 ] 0
6:59:04 PM  Info    11 10 8 [ 42 ] [ 43 ] -1
6:59:04 PM  Info    11 10 9 [ 142 ] [ 174 ] -2
6:59:04 PM  Info    **11 10 10 [ 87 ] [ 107 ] -1**
6:59:04 PM  Info    11 10 11 [ 31 ] [ 28 ] 0
6:59:04 PM  Info    11 10 12 [ 25 ] [ 14 ] 1
6:59:04 PM  Info    11 10 13 [ 52 ] [ 55 ] 2
6:59:04 PM  Info    11 10 14 [ 0 ] [ 0 ] 2
6:59:04 PM  Info    11 10 15 [ 1 ] [ 0 ] 3

A lógica sempre aplica +1 à pontuação que tem um comprimento menor (2 vs. 3 números), independentemente de ser em casa ou fora. Se ambas as pontuações tiverem 2 ou 3 números de comprimento, a pontuação é correta.

Estou usando a função integrada do Google App Scripts, UrlFetchApp.fetch, para solicitar dados de uma URL. Quando você navega para essa URL no navegador, ele apenas inicia um download de arquivo de um arquivo XLSX. Se eu console.log a resposta, é apenas uma sequência de dados ilegíveis. Como posso analisar uma sequência de caracteres que representa o conteúdo deste arquivo XLSX em uma matriz JSON?

Não consigo salvar este arquivo no drive e lê-lo novamente, estou procurando uma maneira de analisar a sequência de resposta diretamente no App Script. O código a seguir usa a biblioteca xlsx.full.min.js. Recebo um erro "Erro durante a análise: Método de compactação ZIP não suportado NaN"

    function parseExcelToJson() {
  try {
    // Step 1: Fetch the Excel file from the URL
    const response = UrlFetchApp.fetch('https://www.nasdaqtrader.com/Content/ProductsServices/TRADING/TRF-Chicago-Test-Securities.xlsx');
    
    // Step 2: Convert the response to a Blob (in memory)
    const blob = response.getBlob();
    
    // Step 3: Convert the Blob to a byte array (needed for xlsx.js)
    const byteArray = blob.getBytes();
    
    // Step 4: Use xlsx.js to parse the byte array
    const data = XLSX.read(byteArray, { type: 'array' });
    
    // Step 5: Access the first sheet
    const sheet = data.Sheets[data.SheetNames[0]];
    
    // Step 6: Convert the sheet to JSON format
    const json = XLSX.utils.sheet_to_json(sheet);
    
    // Step 7: Return the JSON result
    return json;
    
  } catch (e) {
    // Log any errors during parsing
    Logger.log("Error during parsing: " + e.message);
  }
}

Com base em uma planilha do Google contendo 3 colunas:

a coluna 1 contém códigos de departamento, a coluna 2 contém nomes de cidades, a coluna 3 contém nomes de estabelecimentos,

Aqui está o link para as planilhas do Google que estou usando como fonte para o formulário

Estou tentando ter um código que gere um formulário do Google dividido em seções contendo perguntas com itens de resposta filtrados dependendo da resposta da pergunta anterior, ou seja, gostaria que dependendo do código do departamento selecionado, apenas as cidades deste departamento estivessem disponíveis na próxima seção solicitando a seleção de uma cidade, e dependendo da cidade selecionada, apenas os estabelecimentos da cidade estivessem disponíveis na última seção solicitando a seleção de um estabelecimento.

Eu tenho esse código que funciona para departamentos e cidades

veja o formulário do google que é gerado com o código abaixo

const FORM_ID="1OkH_GNjrvOCkiBAXKqmRQq3e1uYxEpE8baqEDVokFso"
let form=FormApp.openById(FORM_ID)
function resetForm(){
let items=form.getItems()
items.forEach(item=>form.deleteItem(item))
}
function createConditionalForm(){
resetForm()
let ws=SpreadsheetApp.getActiveSpreadsheet()
let ss=ws.getSheetByName("Feuille 4")
let table=ss.getDataRange().getValues()
table.shift()
let dptOptions=table.map(row=>row[0])
let singleDptOptions=[]
dptOptions.forEach(option=>{
if(singleDptOptions.indexOf(option)==-1)
singleDptOptions.push(option)
})
let departementQuestion = form.addMultipleChoiceItem().setTitle("Select Region")
let finalDptOptions = []
singleDptOptions.forEach(dptOption=>{
let section=form.addPageBreakItem().setTitle(dptOption)
section.setGoToPage(FormApp.PageNavigationType.SUBMIT)

let cityOptions=table.filter(option=>option[0]==dptOption).map(row=>row[1])
let singleCityOptions=[]
cityOptions.forEach(option=>{
if(singleCityOptions.indexOf(option)==-1)
singleCityOptions.push(option)
})
let cityQuestion = form.addMultipleChoiceItem().setTitle("Select City")
cityQuestion.setChoiceValues(singleCityOptions)
let dptChoice = departementQuestion.createChoice(dptOption, section)
finalDptOptions.push(dptChoice)
})
departementQuestion.setChoices(finalDptOptions)
}

mas não consigo adicionar outra seção com estabelecimentos depois da seleção da cidade.

Aqui está onde estou até agora, mas continuo recebendo mensagens de erro dizendo que a matriz está vazia.

const FORM_ID="Form_ID"
let form=FormApp.openById(FORM_ID)
function resetForm(){
let items=form.getItems()
items.forEach(item=>form.deleteItem(item))
}
function createConditionalForm(){
resetForm()
let ws=SpreadsheetApp.getActiveSpreadsheet()
let ss=ws.getSheetByName("Feuille 4")
let table=ss.getDataRange().getValues()
table.shift()
let dptOptions=table.map(row=>row[0])
let singleDptOptions=[]
dptOptions.forEach(option=>{
if(singleDptOptions.indexOf(option)==-1)
singleDptOptions.push(option)
})
let departementQuestion = form.addMultipleChoiceItem().setTitle("Select Dpt code")
let finalDptOptions = []
singleDptOptions.forEach(dptOption=>{
let section=form.addPageBreakItem().setTitle(dptOption)
let cityOptions=table.filter(option=>option[0]==dptOption).map(row=>row[1])
let singleCityOptions=[]
cityOptions.forEach(option=>{
if(singleCityOptions.indexOf(option)==-1)
singleCityOptions.push(option)
})
let cityQuestion = form.addMultipleChoiceItem().setTitle("Select City")
let finalCityOptions=[]
singleCityOptions.forEach(cityOption=>{
let section2=form.addPageBreakItem().setTitle(cityOption)
section2.setGoToPage(FormApp.PageNavigationType.SUBMIT)
let etabOptions=table.filter(option=>option[0]==cityOption).map(row=>row[2])
let singleEtabOptions=[]
etabOptions.forEach(option=>{
if(singleEtabOptions.indexOf(option)==-1)
singleEtabOptions.push(option)
})
let etabQuestion=form.addMultipleChoiceItem().setTitle("Select Place")
etabQuestion.setChoiceValues(singleEtabOptions)
let cityChoice = cityQuestion.createChoice(cityOption, section2)
finalCityOptions.push(cityChoice)
})
cityQuestion.setChoices(finalCityOptions)
cityQuestion.setChoiceValues(singleCityOptions)
let dptChoice = departementQuestion.createChoice(dptOption, section)
finalDptOptions.push(dptChoice)
})
departementQuestion.setChoices(finalDptOptions)
}

Alguma ideia de como posso adaptar o código inicial?

Obrigado pela ajuda !

Então, eu tenho esse script do CoinMarketCap para pesquisar preços de criptomoedas. Quero modificar esse script onde eu possa aplicá-lo a uma célula e então arrastá-lo e soltá-lo na coluna para ler o símbolo do ticker em outra coluna de célula. Eu sou um completo novato em App Scripts.

function getLatestBTCPrice() {

var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
var apiKey = 'YOUR_API_KEY'; // Replace with your API key
var headers = {
"X-CMC_PRO_API_KEY": apiKey,
"Accept": "application/json"
};

var parameters = {
"symbol": "BTC"
};

var response = UrlFetchApp.fetch(url + "?" + Object.keys(parameters).map(key => key + '=' +
parameters[key]).join('&'), {'headers': headers});
var json = JSON.parse(response.getContentText());
var btcPrice = json.data.BTC.quote.USD.price;

return btcPrice;
}

Foi isso que tentei até agora sem sucesso. A linha com a qual estou tendo problemas é var tickerPrice = json.data.ETC.quote.USD.price; que quero que o ETC atualize, pois ele leu um novo ticker, mas não consigo descobrir.

function getTickerPrice() {


var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest";
var apiKey = 'YOUR_API_KEY'; // Replace with your API key
var headers = {
"X-CMC_PRO_API_KEY": apiKey,
"Accept": "application/json"
};

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var ticker = sheet.getRange('A3').getValue();
var parameters = {
"symbol": ticker
};

var response = UrlFetchApp.fetch(url + "?" + Object.keys(parameters).map(key => key + '=' +
parameters[key]).join('&'), {'headers': headers});
var json = JSON.parse(response.getContentText());
var tickerPrice = json.data.ETC.quote.USD.price;

return tickerPrice;

Estou tentando fazer um loop em Sheets e getLastColumn(). Eu ativo a primeira planilha e obtenho a última coluna, então ativo a próxima planilha, mas dessa vez recebo um erro em getLastColumn(). Estou fazendo isso em um loop for, pode ser esse o problema?

    //sort fab, milling, turning, engraving
    sheet = SpreadsheetApp.getActive().getSheetByName("Fab Schedule");
    sheet.activate;
  
    var lastColumnFab = sheet.getLastColumn();
    var lastColumn = lastColumnFab;
    var lastColumnMilling;
    var lastColumnTurning;
    var lastColumnEngraving;
    var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName(); 
    Logger.log(currentSheet);

    for(a=0;a<3;a++){ // 'a' is for looping through the different sheets
    Logger.log(a);

    // counting non-empty rows 
    var BValues = sheet.getRange("B3:B").getValues();
    var i=0;
    var singleBValue = "";
    //Logger.log(BValues[i]);
    for(i = 0; BValues[i] != ""; i++){
      singleBValue = BValues[i];
      //console.log({i,singleBValue});
    }
    var lastRow = i;
    i=i+3;

    // arrays begin with 0 // all rows and columns begin with 1
    sheet.getRange(3,2,lastRow,lastColumn).activate();
    //sheet.getRange('B3:V').activate(); //number of columns is different on every
    sheet
    var statusColumn = getHeaderNumber("Status")+1; //get header uses array, so it
    starts at 0
    var dueDateColumn = getHeaderNumber("Due Date")+1;
    var engineerColumn = getHeaderNumber("Engineer")+1;
  
    //sort by incomplete->complete, then
    sheet.getActiveRange().sort([{column: statusColumn, ascending: false}, {column: dueDateColumn, ascending: true},{column:engineerColumn, ascending: true}]);
  //sheet.getActiveRange().sort([{column: 14, ascending: false}]);//, {column: 18, ascending: true},{column:2, ascending: true}]);

  sheet.getRange(3,14,lastRow,lastColumn-14).setHorizontalAlignment("center"); //number of columns is different on every sheet
  sheet.getRange(3,1,lastRow,lastColumn).setVerticalAlignment("middle");
  sheet.getRange(3,1,lastRow,lastColumn).setFontFamily("Arial");
  sheet.getRange(3,1,lastRow,lastColumn).setFontSize(10);
  if (a==0){
      sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Milling Schedule");
      sheet.activate();
      var currentSheet = SpreadsheetApp.getActiveSheet().getSheetName(); 
      Logger.log(currentSheet);
      lastColumnMilling = sheet.getLastColumn(); //error?
      lastColumn = lastColumnMilling;
    }

Exception: The coordinates of the range are outside the dimensions of the sheet. Sortbyincompleteanddate @ sort.gs:62