function query()

in gemini/use-cases/sheets-integration/Code.gs [115:188]
61 lines of code
10 McCabe index (conditional complexity)

function query() {
  projectId = getProjectId();
  if (projectId == null) {
    SpreadsheetApp.getUi().alert(
      "A Google Cloud project must be selected using the Setup menu item.",
    );
  }
  const numColumns = SpreadsheetApp.getActiveRange().getNumColumns();
  if (numColumns != 1) {
    SpreadsheetApp.getUi().alert(
      "Exactly one column of prompts must be selected.",
    );
    return null;
  }

  // Populate temporary table in BigQuery with selected data from sheet
  const prompts = sanitizePrompts(SpreadsheetApp.getActiveRange().getValues());
  populateTable(prompts);

  const query =
    `SELECT * FROM ML.GENERATE_TEXT( MODEL \`${datasetId}.${modelId}\`, ` +
    `(SELECT * FROM \`${projectId}.${datasetId}.${tableName}\`), ` +
    `STRUCT(${getMaxOutputTokens()} AS max_output_tokens, ${getTemperature()} AS temperature));`;
  console.log(`Query: ${query}`);

  try {
    response = runQuery(query);
  } catch (error) {
    console.log(error.details.message);
    SpreadsheetApp.getUi().alert(error);
    return null;
  }

  const responseMap = {};
  invalidResponses = false;
  for (const row of response.rows) {
    returnedPrompt = row.f[2].v;
    console.log(`Prompt: ${returnedPrompt}`);

    jsonString = row.f[0].v;
    console.log(jsonString);
    const jsonData = JSON.parse(jsonString);
    console.log(jsonData);
    if (jsonData.candidates) {
      content = jsonData.candidates[0].content;
    }

    // Is it missing content, e.g. due to a finish reason of 4 (RECITATION)?
    var response = "";
    if (!jsonData.candidates || !content) {
      invalidResponses = true;
      SpreadsheetApp.getUi().alert(row);
    } else {
      response = jsonData.candidates[0].content.parts[0].text;
    }
    console.log(`Response: ${response}`);
    responseMap[returnedPrompt] = response;
  }

  if (invalidResponses) {
    SpreadsheetApp.getActiveSpreadsheet().toast(
      "Some prompts did not return a response. Check prompts and safety settings.",
    );
  }

  // Responses come back in any order.
  // Put these in the same order as the selected cells.
  responses = [];
  for (const prompt of prompts) {
    responses.push(responseMap[prompt]);
  }

  writeResponses(responses);
}