Sample AppsScript for exporting an XLSForm in Google Sheets to Excel and publishing it in ODK Central

GoogleSheetToOdkCentral.js

function getGoogleSpreadsheetAsExcel() {
  // Adapted from:
  // https://stackoverflow.com/questions/39765110/google-script-convert-sheet-to-xlsx
  let ss = SpreadsheetApp.getActive();
  let url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
  let options = {
    "method": "get",
    "headers": {"Authorization": "Bearer " + ScriptApp.getOAuthToken()}
  };
  console.log(`Google API request (${options.method}): ${url}`);
  return UrlFetchApp.fetch(url, options).getBlob();
}

function promptForValue(name, example) {
  // Prompt the user for a value, optionally with an example
  let ui = SpreadsheetApp.getUi();
  let prompt = `Please enter ${name}`;
  if (example)
    prompt += ` (e.g., ${example})`;
  let response = ui.prompt(prompt);
  return response.getResponseText();
}

function getPropertyOrPrompt(property, example) {
  // Fetch a property from the script properties service, or fall back to
  // prompting the user and save the value for future use.
  let scriptProperties = PropertiesService.getScriptProperties();
  let value = scriptProperties.getProperty(property);
  if (!value) {
    value = promptForValue(property, example);
    scriptProperties.setProperty(property, value);
  }
  return value;
}

function getBaseUrl() {
  return getPropertyOrPrompt("BASE_URL", "https://odk-central.example.com");
}

function getProjectId() {
  return getPropertyOrPrompt("PROJECT_ID", "123");
}

function getFormId() {
  return getPropertyOrPrompt("FORM_ID", "my_form_id");
}

function centralApiRequest(method, path, contentType, payload, headers) {
  let scriptProperties = PropertiesService.getScriptProperties();
  let token = scriptProperties.getProperty("TOKEN");
  let url = getBaseUrl() + path;

  console.log(`Central API request (${method}): ${url}`);

  var options = {
    "method" : method,
    "payload" : payload,
    "muteHttpExceptions": true, // use more verbose error handling below, instead
    "headers": headers || {}
  };
  if (contentType) {
    options["contentType"] = contentType;
  }
  if (token)
    options["headers"]["Authorization"] = "Bearer " + token;

  response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() != 200)
    throw Error(`API failure (${response.getResponseCode()}): ${response.getContentText()}`);
  return JSON.parse(response.getContentText());
}

function login() {
  // https://odkcentral.docs.apiary.io/#reference/authentication/session-authentication/logging-in
  let scriptProperties = PropertiesService.getScriptProperties();
  let tokenExpiry = scriptProperties.getProperty("TOKEN_EXPIRY");
  if (!tokenExpiry || new Date() > new Date(tokenExpiry)) {
    let body = {
      "email": promptForValue("EMAIL"),
      "password": promptForValue("PASSWORD")
    };
    let tokenData = centralApiRequest("post", "/v1/sessions", "application/json", JSON.stringify(body))
    scriptProperties.setProperty("TOKEN", tokenData.token);
    scriptProperties.setProperty("TOKEN_EXPIRY", tokenData.expiresAt);
  }
}

function publishForm(xlsxBlob) {
  // https://odkcentral.docs.apiary.io/#reference/forms/draft-form/creating-a-draft-form
  let projectId = getProjectId();
  let formId = getFormId();
  let createDraftHeaders = {
    // without this header, a "No extension" error from pyxform is generated
    "X-XlsForm-FormId-Fallback": formId,
  }
  // create the draft
  centralApiRequest(
    "post",
    `/v1/projects/${projectId}/forms/${formId}/draft?ignoreWarnings=true`,
    null, // contentType assigned from blob by UrlFetchApp
    xlsxBlob,
    createDraftHeaders
  );

  // fetch the draft XML
  let draftXml = centralApiRequest("get", `/v1/projects/${projectId}/forms/${formId}/draft.xml`);

  // re-create the draft with the placeholder question names updated
  centralApiRequest(
    "post",
    `/v1/projects/${projectId}/forms/${formId}/draft?ignoreWarnings=true`,
    null, // contentType assigned from blob by UrlFetchApp
    draftXml = draftXml.replace(/_CHANGEME/g, ""),
    createDraftHeaders
  );

  // publish the draft
  centralApiRequest("post", `/v1/projects/${projectId}/forms/${formId}/draft/publish?i`);
}

function main() {
  // let scriptProperties = PropertiesService.getScriptProperties();
  // scriptProperties.deleteAllProperties()
  login();
  let xlsxBlob = getGoogleSpreadsheetAsExcel();
  publishForm(xlsxBlob);
}