' . esc_html__( 'No Google account connection is needed: each feed posts to an Apps Script web app inside your spreadsheet. The feed editor shows the script and the steps.', 'fieldwren' ) . '
'; } /** @return string The Apps Script users paste into the spreadsheet. */ public static function script() { return <<<'JS' /** * Fieldwren → Google Sheets * 1. In the spreadsheet: Extensions → Apps Script. Replace everything with this code. * 2. Set TOKEN to the "Secret token" of the feed (recommended), then Save. * 3. Deploy → New deployment → type "Web app". Execute as: Me. Who has access: Anyone. * 4. Authorise, then copy the Web app URL (ends with /exec) into the feed. * After editing the script, use Deploy → Manage deployments → Edit → New version. */ var TOKEN = ''; function doPost(e) { var lock = LockService.getScriptLock(); lock.waitLock(20000); try { var data = JSON.parse(e.postData.contents); if (TOKEN && data.token !== TOKEN) { return out({ ok: false, error: 'Invalid token' }); } var ss = SpreadsheetApp.getActiveSpreadsheet(); var name = data.sheet || 'Entries'; var sheet = ss.getSheetByName(name) || ss.insertSheet(name); var row = data.row || {}; var lastCol = sheet.getLastColumn(); var headers = lastCol ? sheet.getRange(1, 1, 1, lastCol).getValues()[0] : []; Object.keys(row).forEach(function (k) { if (headers.indexOf(k) === -1) { headers.push(k); sheet.getRange(1, headers.length).setValue(k).setFontWeight('bold'); } }); if (!lastCol) { sheet.setFrozenRows(1); } sheet.appendRow(headers.map(function (h) { return Object.prototype.hasOwnProperty.call(row, h) ? row[h] : ''; })); return out({ ok: true, row: sheet.getLastRow() }); } catch (err) { return out({ ok: false, error: String(err) }); } finally { lock.releaseLock(); } } function doGet() { return out({ ok: true, ready: true }); } function out(o) { return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON); } JS; } public function feed_fields( $form_id ) { return array( array( 'key' => 'setup', 'type' => 'notice', 'label' => __( 'Setup', 'fieldwren' ), 'html' => '' . esc_html__( 'Headers are added automatically from the column names; values that start with =, +, - or @ are sent as text.', 'fieldwren' ) . '
', 'code' => self::script(), ), array( 'key' => 'url', 'type' => 'secret', 'url' => true, 'label' => __( 'Web app URL', 'fieldwren' ), 'required' => true, 'placeholder' => 'https://script.google.com/macros/s/…/exec' ), array( 'key' => 'token', 'type' => 'secret', 'label' => __( 'Secret token', 'fieldwren' ), 'desc' => __( 'Optional; must match TOKEN in the script.', 'fieldwren' ) ), array( 'key' => 'sheet', 'type' => 'text', 'label' => __( 'Sheet (tab) name', 'fieldwren' ), 'default' => 'Entries', 'desc' => __( 'Created when missing.', 'fieldwren' ) ), array( 'key' => 'columns', 'type' => 'select', 'label' => __( 'Columns', 'fieldwren' ), 'default' => 'all', 'no_empty' => true, 'options' => array( 'all' => __( 'All fields (header = field label)', 'fieldwren' ), 'selected' => __( 'Selected fields', 'fieldwren' ), 'custom' => __( 'Custom columns', 'fieldwren' ), ), ), array( 'key' => 'fields', 'type' => 'fields', 'label' => __( 'Fields', 'fieldwren' ), 'show_if' => array( 'columns' => 'selected' ) ), array( 'key' => 'custom', 'type' => 'kv', 'label' => __( 'Custom columns', 'fieldwren' ), 'key_label' => __( 'Column header', 'fieldwren' ), 'value_label' => __( 'Value', 'fieldwren' ), 'show_if' => array( 'columns' => 'custom' ) ), array( 'key' => 'meta', 'type' => 'toggle', 'label' => __( 'Entry columns', 'fieldwren' ), 'text' => __( 'Add "Entry ID", "Submitted at" and "Page URL" columns', 'fieldwren' ), 'default' => true ), ); } /** @return string Cell value safe from formula injection. */ private static function cell( $v ) { $v = (string) $v; return '' !== $v && in_array( $v[0], array( '=', '+', '-', '@' ), true ) && ! is_numeric( $v ) ? "'" . $v : $v; } public function process( array $feed, array $entry, array $values ) { $s = $feed['settings']; $url = trim( (string) ( $s['url'] ?? '' ) ); if ( ! preg_match( '#^https://script\.google(usercontent)?\.com/#', $url ) ) { return new \WP_Error( 'fwren_bad_url', __( 'The Apps Script web app URL is missing or not a script.google.com URL.', 'fieldwren' ) ); } $this->secrets[] = $url; $row = array(); $mode = $s['columns'] ?? 'all'; if ( ! empty( $s['meta'] ) ) { $meta = $this->entry_meta(); $row[ __( 'Entry ID', 'fieldwren' ) ] = (string) $meta['entry_id']; $row[ __( 'Submitted at', 'fieldwren' ) ] = wp_date( 'Y-m-d H:i:s' ); } if ( 'custom' === $mode ) { foreach ( $this->kv( 'custom' ) as $h => $v ) { $row[ $h ] = self::cell( $v ); } } else { foreach ( $this->data_rows( 'selected' === $mode ? (array) ( $s['fields'] ?? array() ) : array() ) as $r ) { $h = $r[0]; $n = 2; while ( isset( $row[ $h ] ) ) { $h = $r[0] . ' ' . $n++; } $row[ $h ] = self::cell( $r[1] ); } } if ( ! empty( $s['meta'] ) ) { $row[ __( 'Page URL', 'fieldwren' ) ] = (string) ( $entry['source_url'] ?? '' ); } $token = (string) ( $s['token'] ?? '' ); if ( $token ) { $this->secrets[] = $token; } $payload = array( 'token' => $token, 'sheet' => $this->val( $s['sheet'] ?? '' ) ? $this->val( $s['sheet'] ) : 'Entries', 'row' => (object) $row ); // Apps Script answers 302 → script.googleusercontent.com; the row is already written then. $res = $this->http( 'POST', $url, array( 'body' => $payload, 'redirection' => 0, 'timeout' => 30 ) ); if ( is_wp_error( $res ) ) { return $res; } $body = $res['body']; if ( in_array( $res['code'], array( 301, 302, 303, 307 ), true ) ) { $loc = $res['headers']['location'] ?? ''; $loc = is_array( $loc ) ? end( $loc ) : $loc; if ( $loc ) { $next = $this->http( 'GET', (string) $loc, array( 'redirection' => 2 ) ); $body = is_wp_error( $next ) ? null : $next['body']; } } if ( is_array( $body ) && isset( $body['ok'] ) && ! $body['ok'] ) { return new \WP_Error( 'fwren_sheets', sprintf( /* translators: %s error */ __( 'Apps Script error: %s', 'fieldwren' ), (string) ( $body['error'] ?? '' ) ) ); } if ( is_string( $body ) && false !== stripos( $body, '